Different ways to get Identity of New Inserted Rows in SQL Server

There are different methods to know the Identity Value of a newly added row.

Let us consider the following example:

--Create a Table
CREATE TABLE test
(
id int identity(1,1),
names varchar(100)
)

--Insert Data
INSERT INTO test(names) SELECT 'testing'

--Get Identity Value that is Populated in the Current Scope
SELECT scope_identity()


--Get Identity value that is Populated in the Current Session
SELECT @@identity

--Get Identity value that is Populated in the Table
--Regardless of Scope and Session
SELECT ident_current('test')

Note that first two methods wont give correct values if data are added to the ‘different tables’.

OUTPUT

image

at

4 comments:

  1. satyanAugust 8, 2010 at 11:55 PM

    this is very good info madhivnan that helped us solve a issue. I had no idea on local scope and session identity.

    ReplyDelete
  2. Adam ZochowskiAugust 9, 2010 at 7:51 AM

    ident_current() will cause problems with concurency. Don't use!

    @@identity will cause problems with triggers. Don't use!

    Always use scope_identity() !


    Cheers

    ReplyDelete
  3. UnknownAugust 10, 2010 at 5:58 AM

    You also could use the OUTPUT Clause.

    DECLARE @VarGen TABLE (idEmployee INTEGER)
    insert into employee(field1, field2)
    OUTPUT Inserted.idEmployee INTO @VarGen
    values(value1, value2)

    you have the identity in:
    select idEmployee from @VarGen

    Emito.

    ReplyDelete
  4. AnonymousOctober 13, 2010 at 2:35 PM

    If you are inserting multiple records with one insert statement and need to return the ID values for all rows inserted, try this:

    DECLARE @Values table(
    ID int not null identity(1,1) primary key,
    Ident int)

    INSERT Messages (
    ID, date, CustomerNumber, CheckNumber)
    OUTPUT INSERTED.Ident
    INTO @Values (Ident)
    select
    from #AutoCloseMessage t

    You will get a result of @Values containing all of the Idents that were inserted into the Messages table

    ReplyDelete