SQL How to get the id of values I just INSERTed

Question

I inserted some values into a table  There is a column whose value is auto-generated  In the next statement of my code  I want to retrieve this value   Can you tell me how to do it the right way

User · Answer

For SQL 2005   Assuming the following table definition   CREATE TABLE  dbo   Test        ID   int  IDENTITY 1 1  NOT NULL       somevalue   nchar  10  NULL       You can use the following   INSERT INTO Test somevalue  OUTPUT INSERTED ID VALUES  asdfasdf     Which will return the value of the ID column

User · Answer

An Environment Based Oracle Solution   CREATE OR REPLACE PACKAGE LAST AS ID NUMBER  FUNCTION IDENT RETURN NUMBER  END     CREATE OR REPLACE PACKAGE BODY LAST AS FUNCTION IDENT RETURN NUMBER IS     BEGIN     RETURN ID      END  END      CREATE TABLE Test          TestID            INTEGER       Field1      int      Field2      int     CREATE SEQUENCE Test seq   CREATE OR REPLACE TRIGGER Test itrig BEFORE INSERT ON Test FOR EACH ROW DECLARE seq val number  BEGIN IF  new TestID IS NULL THEN     SELECT Test seq nextval INTO seq val FROM DUAL       new TestID    seq val      Last ID    seq val  END IF  END     To get next identity value  SELECT LAST IDENT FROM DUAL

User · Answer

There s no standard way to do it  just as there is no standard way to create auto-incrementing IDs   Here are two ways to do it in PostgreSQL  Assume this is your table   CREATE TABLE mytable     id SERIAL PRIMARY KEY    lastname VARCHAR NOT NULL    firstname VARCHAR      You can do it in two statements as long as they re consecutive statements in the same connection  this will be safe in PHP with connection pooling because PHP doesn t give the connection back to the pool until your script is done    INSERT INTO mytable  lastname  firstname  VALUES   Washington    George    SELECT lastval      lastval   gives you the last auto-generated sequence value used in the current connection   The other way is to use PostgreSQL s RETURNING clause on the INSERT statement   INSERT INTO mytable  lastname  VALUES   Cher   RETURNING id    This form returns a result set just like a SELECT statement  and is also handy for returning any kind of calculated default value

User · Answer

IDENTITY is not scope safe and will get you back the id from another table if you have an insert trigger on the original table  always use SCOPE IDENTITY

User · Answer

An important note is that using vendor SQL queries to retrieve the last inserted ID are safe to use without fearing about concurrent connections   I always thought that you had to create a transaction in order to INSERT a line and then SELECT the last inserted ID in order to avoid retrieving an ID inserted by another client   But these vendor specific queries always retrieve the last inserted ID for the current connection to the database  It means that the last inserted ID cannot be affected by other client insertions as long as they use their own database connection

User · Answer

From the site i found out the following things   SQL SERVER       IDENTITY vs SCOPE IDENTITY   vs IDENT CURRENT     Retrieve Last Inserted Identity of Record March 25  2007 by pinaldave   SELECT   IDENTITY It returns the last IDENTITY value produced on a connection  regardless of the table that produced the value  and regardless of the scope of the statement that produced the value    IDENTITY will return the last identity value entered into a table in your current session  While   IDENTITY is limited to the current session  it is not limited to the current scope  If you have a trigger on a table that causes an identity to be created in another table  you will get the identity that was created last  even if it was the trigger that created it   SELECT SCOPE IDENTITY   It returns the last IDENTITY value produced on a connection and by a statement in the same scope  regardless of the table that produced the value  SCOPE IDENTITY    like   IDENTITY  will return the last identity value created in the current session  but it will also limit it to your current scope as well  In other words  it will return the last identity value that you explicitly created  rather than any identity that was created by a trigger or a user defined function   SELECT IDENT CURRENT    tablename     It returns the last IDENTITY value produced in a table  regardless of the connection that created the value  and regardless of the scope of the statement that produced the value  IDENT CURRENT is not limited by scope and session  it is limited to a specified table  IDENT CURRENT returns the identity value generated for a specific table in any session and any scope

User · Answer

In TransactSQL  you can use OUTPUT clause to achieve that   INSERT INTO my table col1 col2 col3  OUTPUT INSERTED id VALUES  col1Value   col2Value   col3Value     FRI  http   msdn microsoft com en-us library ms177564 aspx

User · Answer

This is how I ve done it using parameterized commands   MSSQL   INSERT INTO MyTable  Field1  Field2  VALUES   Value1   Value2     SELECT SCOPE IDENTITY       MySQL   INSERT INTO MyTable  Field1  Field2  VALUES   Value1   Value2    SELECT LAST INSERT ID

User · Answer

insert the row with a known guid   fetch the autoId-field with this guid     This should work with any kind of database

User · Answer

If your using PHP and MySQL you can use the mysql insert id   function which will tell you the ID of item you Just instered  But without your Language and DBMS I m just shooting in the dark here

User · Answer

This is how I do my store procedures for MSSQL with an autogenerated ID   CREATE PROCEDURE  dbo   InsertProducts       id             INT               NULL OUT       name           VARCHAR 150       NULL       desc           VARCHAR 250       NULL  AS      INSERT INTO dbo Products         Name          Description      VALUES          name           desc       SET  id   SCOPE IDENTITY

User · Answer

If you are working with Oracle      Inset into Table  Fields      values  Values     RETURNING  List of Fields     INTO  variables       example      INSERT INTO PERSON  NAME  VALUES   JACK   RETURNING ID PERSON INTO vIdPerson   or if you are calling from    Java with a CallableStatement  sry  it s my field      INSERT INTO PERSON  NAME  VALUES   JACK   RETURNING ID PERSON INTO     and declaring an autput parameter for the statement

User · Answer

Again no language agnostic response  but in Java it goes like this     Connection conn   Database getCurrent   getConnection      PreparedStatement ps    conn prepareStatement insertSql  Statement RETURN GENERATED KEYS   try         ps executeUpdate          ResultSet rs   ps getGeneratedKeys          rs next          long primaryKey   rs getLong 1       finally         ps close

User · Answer

SELECT   Scope Identity as Id   There is also   identity  but if you have a trigger  it will return the results of something that happened during the trigger  where scope identity respects your scope

User · Answer

Ms SQL Server  this is good solution even if you inserting more rows    Declare  tblInsertedId table  Id int not null    INSERT INTO Test   Title    Text    OUTPUT inserted Id INTO  tblInsertedId  Id   SELECT  Title    Text  FROM AnotherTable   select Id from  tblInsertedId

User · Answer

Remember that   IDENTITY returns the most recently created identity for your current connection  not necessarily the identity for the recently added row in a table  You should always use SCOPE IDENTITY   to return the identity of the recently added row

User · Answer

Rob s answer would be the most vendor-agnostic  but if you re using MySQL the safer and correct choise would be the built-in LAST INSERT ID   function

User · Answer

Simplest answer   command ExecuteScalar     by default returns the first column  Return Value Type  System Object The first column of the first row in the result set  or a null reference  Nothing in Visual Basic  if the result set is empty  Returns a maximum of 2033 characters   Copied from MSDN

User · Answer

This works very nicely in SQL 2005   DECLARE  inserted ids TABLE   id  INT    INSERT INTO  dbo   some table    col1   col2   col3   col4   col5   col6   OUTPUT INSERTED  id  INTO  inserted ids VALUES   col1  col2  col3  col4  col5  col6    It has the benefit of returning all the IDs if your INSERT statement inserts multiple rows

User · Answer

What database are you using  As far as I m aware  there is no database agnostic method for doing this

User · Answer

sql    INSERT INTO MyTable  Name  VALUES   Name             SELECT CAST scope identity   AS int    SqlCommand cmd   new SqlCommand sql  conn   int newId    int cmd ExecuteScalar

[sql-server] SQL: How to get the id of values I just INSERTed?

Examples related to sql-server