Getting the identity row value for an Insert operation in SQL

Question

All the years I've played with SQL, and I never thought to ask this question.

If I am adding (inserting) a row into a table, and it has an identity column, how might I retrieve the identity for this new row? I am keen on hearing a solution to this, as I fear I may be operating on a database with some very similar or duplicate rows.

possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) and many others. — Martin Smith, Nov 18 '12 at 09:12

score 1 · Accepted Answer · answered Nov 18 '12 at 02:17

1

Use SCOPE_IDENTITY function to obtain the last inserted identity in the current scope.

answered Nov 18 '12 at 02:17

Sergey Kalinichenko

714,442
84
1,110
1,523

Hmm. And if I pair up an Insert statement with a Select statement, I may get my identity. Nice. – user978122 Nov 19 '12 at 01:10

score 1 · Answer 2 · answered Nov 18 '12 at 08:49

There are three main functions/ways how to retrieve the value of IDENTITY column. Please follow the first link to brief explanation. The second (MSDN) provides clear examples and comparison:

Three ways how to get identity: @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('TableName')

While @@IDENTITY is most likely "the-most-often-used", because of its implementation it does not have to always return expected identity value. read more here

Getting the identity row value for an Insert operation in SQL

2 Answers2