Thread: How to capture MAX id value into a variable

Dear group,
 I have two tables (say Employee and Duty).  Duty
table is linked to employee table by employee_id key.
Other than this there is not other data fields
overlapping the tables.

I have my data in an Excel sheet.  The first 3 columns
of this data sheet should go into Employee table and
the next 3 columns to DUTY table.

I am writing a python script that write SQL
statements:

INSERT INTO EMPLOYEE(<columns>) VALUES(
col1,col2,col3);

SELECT INTO X = SELECT MAX(employee_id) from employee;

INSERT INTO DUTY(<cols>) VALUES(X, col4,col4,col6);


Here I want to capture the MAX employee_id into a
variable X and then I want to write it into the  next
table. By this I am filling the foreign key
(employee_id) in DUTY table.

My question:

1. How to capture select max value into a variable X
as an SQL statement?
2. Is this a better approach or is there any other
alternative that I can choose.

Please help me.
thanks

Kumar.



__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

On Wed, Nov 17, 2004 at 03:51:47PM -0800, Kumar S wrote:
>
> INSERT INTO EMPLOYEE(<columns>) VALUES(col1,col2,col3);
> SELECT INTO X = SELECT MAX(employee_id) from employee;
> INSERT INTO DUTY(<cols>) VALUES(X, col4,col4,col6);

If employee_id gets a default value from a sequence (e.g., if you
defined it as SERIAL) then you can use currval() to get the most
recently obtained value from the sequence.

INSERT INTO EMPLOYEE (...) VALUES (...);
INSERT INTO DUTY (...) VALUES (currval('employee_employee_id_seq'), ...);

currval() returns the most recently obtained value in the current
connection, so it's safe to use even if other connections are
updating the sequence at the same time.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/