Best way to get identity of inserted row

Question

What is the best way to get IDENTITY of inserted row   I know about   IDENTITY and IDENT CURRENT and SCOPE IDENTITY but don t understand the pros and cons attached to each   Can someone please explain the differences and when I should be using each

User · Answer

The best  read  safest  way to get the identity of a newly-inserted row is by using the output clause   create table TableWithIdentity              IdentityColumnName int identity 1  1  not null primary key                      -- type of this table s column must match the type of the -- identity column of the table you ll be inserting into declare  IdentityOutput table   ID int    insert TableWithIdentity              output inserted IdentityColumnName into  IdentityOutput values               select  IdentityValue    select ID from  IdentityOutput

User · Answer

Create a uuid and also insert it to a column  Then you can easily identify your row with the uuid  Thats the only 100  working solution you can implement  All the other solutions are too complicated or are not working in same edge cases  E g    1  Create row  INSERT INTO table  uuid  name  street  zip           VALUES   2f802845-447b-4caa-8783-2086a0a8d437    Peter    Mainstreet 7    88888      2  Get created row  SELECT   FROM table WHERE uuid  2f802845-447b-4caa-8783-2086a0a8d437

User · Answer

I m saying the same thing as the other guys  so everyone s correct  I m just trying to make it more clear     IDENTITY returns the id of the last thing that was inserted by your client s connection to the database  Most of the time this works fine  but sometimes a trigger will go and insert a new row that you don t know about  and you ll get the ID from this new row  instead of the one you want  SCOPE IDENTITY   solves this problem  It returns the id of the last thing that you inserted in the SQL code you sent to the database  If triggers go and create extra rows  they won t cause the wrong value to get returned  Hooray  IDENT CURRENT returns the last ID that was inserted by anyone  If some other app happens to insert another row at an unforunate time  you ll get the ID of that row instead of your one   If you want to play it safe  always use SCOPE IDENTITY    If you stick with   IDENTITY and someone decides to add a trigger later on  all your code will break

User · Answer

Add  SELECT CAST scope identity   AS int     to the end of your insert sql statement  then  NewId   command ExecuteScalar     will retrieve it

User · Answer

From MSDN    IDENTITY  SCOPE IDENTITY  and IDENT CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table    IDENTITY and SCOPE IDENTITY will return the last identity value generated in any table in the current session  However  SCOPE IDENTITY returns the value only within the current scope    IDENTITY is not limited to a specific scope  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  For more information  see IDENT CURRENT    IDENT CURRENT is a function which takes a table as a argument    IDENTITY may return confusing result when you have an trigger on the table SCOPE IDENTITY is your hero most of the time

User · Answer

IDENTITY returns the last identity value generated for any table in the current session  across all scopes   You need to be careful here  since it s across scopes   You could get a value from a trigger  instead of your current statement  SCOPE IDENTITY   returns the last identity value generated for any table in the current session and the current scope   Generally what you want to use  IDENT CURRENT  tableName   returns the last identity value generated for a specific table in any session and any scope   This lets you specify which table you want the value from  in case the two above aren t quite what you need  very rare    Also  as  Guy Starbuck mentioned   You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into   The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement   Since it s scoped to the specific statement  it s more straightforward than the other functions above   However  it s a little more verbose  you ll need to insert into a table variable temp table and then query that  and it gives results even in an error scenario where the statement is rolled back   That said  if your query uses a parallel execution plan  this is the only guaranteed method for getting the identity  short of turning off parallelism   However  it is executed before triggers and cannot be used to return trigger-generated values

User · Answer

Even though this is an older thread  there is a newer way to do this which avoids some of the pitfalls of the IDENTITY column in older versions of SQL Server  like gaps in the identity values after server reboots  Sequences are available in SQL Server 2016 and forward which is the newer way is to create a SEQUENCE object using TSQL   This allows you create your own numeric sequence object in SQL Server and control how it increments    Here is an example    CREATE SEQUENCE CountBy1       START WITH 1       INCREMENT BY 1     GO     Then in TSQL you would do the following to get the next sequence ID   SELECT NEXT VALUE FOR CountBy1 AS SequenceID GO   Here are the links to CREATE SEQUENCE and NEXT VALUE FOR

User · Answer

After Your Insert Statement you need to add this  And Make sure about the table name where data is inserting You will get current row no where row affected just now by your insert statement     IDENT CURRENT  tableName

User · Answer

When you use Entity Framework  it internally uses the OUTPUT technique to return the newly inserted ID value  DECLARE  generated keys table  Id  uniqueidentifier   INSERT INTO TurboEncabulators StatorSlots  OUTPUT inserted TurboEncabulatorID INTO  generated keys VALUES  Malleable logarithmic casing     SELECT t  TurboEncabulatorID   FROM  generated keys AS g     JOIN dbo TurboEncabulators AS t     ON g Id   t TurboEncabulatorID  WHERE   ROWCOUNT  gt  0   The output results are stored in a temporary table variable  joined back to the table  and return the row value out of the table   Note  I have no idea why EF would inner join the ephemeral table back to the real table  under what circumstances would the two not match    But that s what EF does    This technique  OUTPUT  is only available on SQL Server 2008 or newer   Edit - The reason for the join  The reason that Entity Framework joins back to the original table  rather than simply use the OUTPUT values is because EF also uses this technique to get the rowversion of a newly inserted row    You can use optimistic concurrency in your entity framework models by using the Timestamp attribute    public class TurboEncabulator      public String StatorSlots       Timestamp     public byte   RowVersion   get  set        When you do this  Entity Framework will need the rowversion of the newly inserted row   DECLARE  generated keys table  Id  uniqueidentifier   INSERT INTO TurboEncabulators StatorSlots  OUTPUT inserted TurboEncabulatorID INTO  generated keys VALUES  Malleable logarithmic casing     SELECT t  TurboEncabulatorID   t  RowVersion  FROM  generated keys AS g     JOIN dbo TurboEncabulators AS t     ON g Id   t TurboEncabulatorID  WHERE   ROWCOUNT  gt  0   And in order to retrieve this Timetsamp you cannot use an OUTPUT clause    That s because if there s a trigger on the table  any Timestamp you OUTPUT will be wrong    Initial insert  Timestamp  1 OUTPUT clause outputs timestamp  1 trigger modifies row  Timestamp  2   The returned timestamp will never be correct if you have a trigger on the table  So you must use a separate SELECT   And even if you were willing to suffer the incorrect rowversion  the other reason to perform a separate SELECT is that you cannot OUTPUT a rowversion into a table variable   DECLARE  generated keys table  Id  uniqueidentifier   Rowversion  timestamp   INSERT INTO TurboEncabulators StatorSlots  OUTPUT inserted TurboEncabulatorID  inserted Rowversion INTO  generated keys VALUES  Malleable logarithmic casing      The third reason to do it is for symmetry  When performing an UPDATE on a table with a trigger  you cannot use an OUTPUT clause  Trying do UPDATE with an OUTPUT is not supported  and will give an error    Cannot use UPDATE with OUTPUT clause when a trigger is on the table   The only way to do it is with a follow-up SELECT statement   UPDATE TurboEncabulators SET StatorSlots    Lotus-O deltoid type  WHERE   TurboEncabulatorID   1  AND  RowVersion   792    SELECT RowVersion FROM TurboEncabulators WHERE   ROWCOUNT  gt  0 AND TurboEncabulatorID   1

User · Answer

I believe the safest and most accurate method of retrieving the inserted id would be  using the output clause  for example  taken from the following MSDN article   USE AdventureWorks2008R2  GO DECLARE  MyTableVar table  NewScrapReasonID smallint                             Name varchar 50                              ModifiedDate datetime   INSERT Production ScrapReason     OUTPUT INSERTED ScrapReasonID  INSERTED Name  INSERTED ModifiedDate         INTO  MyTableVar VALUES  N Operator error   GETDATE      --Display the result set of the table variable  SELECT NewScrapReasonID  Name  ModifiedDate FROM  MyTableVar  --Display the result set of the table  SELECT ScrapReasonID  Name  ModifiedDate  FROM Production ScrapReason  GO

User · Answer

One other way to guarantee the identity of the rows you insert is to specify the identity values and use the SET IDENTITY INSERT ON and then OFF  This guarantees you know exactly what the identity values are  As long as the values are not in use then you can insert these values into the identity column   CREATE TABLE  foo            fooid   INT IDENTITY NOT NULL        fooname VARCHAR 20         SELECT   Identity            AS    Identity           Scope identity        AS  SCOPE IDENTITY             Ident current   Foo   AS  IDENT CURRENT    SET IDENTITY INSERT  foo ON   INSERT INTO  foo               fooid                fooname   VALUES       1                 one                  2                 Two     SET IDENTITY INSERT  foo OFF   SELECT   Identity            AS    Identity           Scope identity        AS  SCOPE IDENTITY             Ident current   Foo   AS  IDENT CURRENT    INSERT INTO  foo               fooname   VALUES        Three     SELECT   Identity            AS    Identity           Scope identity        AS  SCOPE IDENTITY             Ident current   Foo   AS  IDENT CURRENT    -- YOU CAN INSERT   SET IDENTITY INSERT  foo ON   INSERT INTO  foo               fooid                fooname   VALUES       10                 Ten                  11                 Eleven     SET IDENTITY INSERT  foo OFF   SELECT   Identity            AS    Identity           Scope identity        AS  SCOPE IDENTITY             Ident current   Foo   AS  IDENT CURRENT    SELECT    FROM    foo    This can be a very useful technique if you are loading data from another source or merging data from two databases etc

User · Answer

ALWAYS use scope identity    there s NEVER a need for anything else

User · Answer

IDENTITY is the last identity inserted using the current SQL Connection   This is a good value to return from an insert stored procedure  where you just need the identity inserted for your new record  and don t care if more rows were added afterward   SCOPE IDENTITY is the last identity inserted using the current SQL Connection  and in the current scope -- that is  if there was a second IDENTITY inserted based on a trigger after your insert  it would not be reflected in SCOPE IDENTITY  only the insert you performed  Frankly  I have never had a reason to use this   IDENT CURRENT tablename  is the last identity inserted regardless of connection or scope   You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into

User · Answer

I can t speak to other versions of SQL Server  but in 2012  outputting directly works just fine   You don t need to bother with a temporary table   INSERT INTO MyTable OUTPUT INSERTED ID VALUES         By the way  this technique also works when inserting multiple rows   INSERT INTO MyTable OUTPUT INSERTED ID VALUES                                   Output  ID 2 3 4

[sql] Best way to get identity of inserted row?

Examples related to sql

Examples related to sql-server

Examples related to tsql