Wednesday, September 30, 2009

Get the id of the newly inserted row

There are times when we need to retrieve the last inserted identity of a row. Here is a scenario


  1. You need to insert a record into a table
  2. You need the Id of the row you inserted, so you can use it to insert rows into tables referencing the first row


SQL Server has the options:


SCOPE_IDENTITY()– Gives the last identity value that was generated in the current scope. In other words, it will give the last identity value that you have explicitly created.



@@Identity –Gives the last identity value that was generated in the current session but in any scope.


IDENT_CURRENT(’tablename’) – Gives the last identity value produced in the table regardless if the connection it the scope.



In our scenario, SCOPE_IDENTITY() works perfect to return the identity of the recently added row.