CODEWITHSUNDEEP
sqlt-sqlinformixscope-identity
Anyname Donotcare
Anyname Donotcare

Reputation: 11393

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

Upvotes: 1

Views: 3972

Answers (2)

tom502
tom502

Reputation: 701

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

Upvotes: 2

Andre Gross
Andre Gross

Reputation: 263

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.

Upvotes: 0

Related Questions