Insert and Obtain Identity ID 2

[btgroup]

Hi,

Can someone give me the syntax for the best way to go about inserting a row into a table with an auto-incrementing identity column as a primary key and then obtaining the new value after the insert? I am concerned about multiple users inserting at the same time and retrieving the wrong value. For instance, I could perform the insert and then select the max value, but if two people insert at the same time and my select is performed after the second insert, I lose the first insert value.

I imagine I need to do something with table locking, but I am not sure how to go about it.

Thanks in advance for your help.

 

[JamesLean]

Look up @@IDENTITY or SCOPE_IDENTITY in BOL.

--James

 

[btgroup]

Thank you, but I didn't see anything about making sure I don't lose an ID when two users insert at once. Specifically, I am trying to obtain an order ID after someone places an order. So I believe I would need something similar to the following:

lock table
insert row
retrieve id from newly added row
unlock table

The @@identity will give me the id, but I don't know if it is the most current.

 

[JamesLean]

@@IDENTITY will only return the last inserted identity value from the current session (connection). You won't pick up another user's value.

SCOPE_IDENTITY goes one step further and only returns the last value from the current scope. So if you have a trigger on your main table that then inserts a row into another table with an identity column, SCOPE_IDENTITY will return the one from the main table, not the subsequent insert. @@IDENTITY would return the value from the second table, which isn't what you want in this case.

--James

 

[SQLSister]

use scope_identity not @@identity. @@identity will give you the wrong identity value at times as it take the most current identity value, so if you have a trigger onthe table which also inserts toa table with an identity field, that is the value you would get with @@identity.

When you use scope_identity(), you will alsowys want to set it's value to a variable as it can change as the stored procedure runs forward. So ususally what you have is something like this
Insert table1 (col1, col2)
Values(@col1, @col2)
Set @Id = scope_identity()
Insert Table2 (col1, col2)
Values (@id, @Col2)


Of course you need to properly deifne your variables but you get the idea.

You don't need to lock the table as SQL server will not give out the same identity value more than once.

 

[btgroup]

Thanks for the great explanations folks! I didn't realize the difference between the two and that they do the locking for you. This is perfect!