SQL: Get Identity of New Inserted Rows

Monday, January 17, 2011

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 won’t give correct values if data are added to the ‘different tables’.

SQL