How to return the last primary key inserted

问题:

I ask about alternative or similar query in informix to perform the following:

INSERT INTO days (depcode,studycode,batchnum) values (1,2,3);SELECT SCOPE_IDENTITY();

I want a query to return the SCOPE_IDENTITY() during insertion statement

回答1:

I know that in t-sql you have the OUTPUT statement? Where [KEY] is the column name of your primary key and @OUT_KEY is a variable you need to declare

INSERT INTO days
(
  depcode,
  studycode,
  batchnum
)
OUTPUT INSERTED.[KEY] INTO @OUT_KEY
VALUES
(
  1,2,3
)

EDIT

For informix you can use

SELECT DBINFO( 'sqlca.sqlerrd1' )
FROM systables
WHERE tabid = 1;

Presuming your pk column is SERIAL

回答2:

I use this sql statement Select @@Identity after I inserted rows. It gives me the ID of the last inserted row. I´m using an accessdatabase. I don´t know if it work with your database.