Determining the PK value of a new record

[jalbao]

i'm using sql server 2k


after i insert a record into my table, how can i retrieve the value of the new pk value (identity) that sql sever generated. ideally, i would like to accomplish this without having to use another query [example select Max(pk)]

tia

 

[balves]

SELECT @@IDENTITY immediately after your INSERT statement.

Put both statements in a transaction.

 

[Glitchbane]

Just a little warning about triggers:
If you insert to a table and generate a new Identity, but that insert fires a trigger that makes another insert on another table with an identity, the @@IDENTITY value you get back right after the first insert will actually be from the insert fired by the trigger.

 

[jalbao]

thx glitchbane - i'll keep that in mind