Transaction using @@IDENTITY or IDENT_CURRENT() Clarification
Posted on 2006-11-29
I have the following scenario in a SQL2005 DB environment:
INSERT INTO tbl1 (col1,col2,col3) VALUES('foo','bar',stuff')
DECLARE @IC int
SET @IC = IDENT_CURRENT('tbl1')
INSERT INTO tbl2 (tbl1ID,tbl1notet) VALUES(@IC,'blahblah')
When a new record is inserted into tbl1, a tbl2 record must also be inserted using the IDENTITY value from the tbl1 insert. The transaction shown works perfectly, but if I understand correctly (quoting from MSDN):
# IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
# @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
In my particular case, I must use the identity from the current session only, which makes it appear that I should use @@IDENTITY, but I'm not sure how I can use this in the transaction shown above.
Thank you in advance,