Friday, June 03, 2005

How to retrieve the identity-id of last inserted record in ms sql server ?

In many cases you might require to read back auto-generated id after insertion, specially when you want to use the id as foreign key into other table.

Let's have an example to understand it well.
Table: Users ( UserID - Autogenerate id, UserName, LastName, Address )

Composite key = UserName, LastName, Address

Now, after an insert statement you would like to know the value of UserID. Have a look at the solutions

Method: 1
insert into Users(UserName, LastName, Address) values('Hello', 'world', 'Blabla !')
select userid from users where UserName = 'Hello' and LastName='world' and Address='Blabla !'

Method: 2
MS SQL Server provides set of global variables for different users. Instead of method 1, i would go for the one below...
insert into Users(UserName, LastName, Address) values('Hello', 'world', 'Blabla !')
[ select @@identity ]
[ select * from Users where id = @@identity]
[ set @localVarible = @@identity ]
[ return @@identity ]


@@identity holds last inserted record's unique auto-generated id. If your insert statement fails to execute, @@identity holds null.

Thanks!

Dhananjay