Transaction using @@IDENTITY or IDENT_CURRENT() Clarification

Hello All,

I have the following scenario in a SQL2005 DB environment:

BEGIN TRANSACTION
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')
COMMIT

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,
Mike
mwheeler_fsdAsked:
Who is Participating?
 
Brendt HessConnect With a Mentor Senior DBACommented:
The difference between the three values:

@@Identity is the last identity generated within the current session.  If a trigger on Tbl1 adds a record into another table with an identity field, it is the identity field from that insert that @@Identity would return.

 IDENT_CURRENT returns the last identity generated on a given table.  If someone else performs an INSERT Operation simultaneously with your INSERT, it is possible for his ID value to be generated before your IDENT_CURRENT call.  In this case, the identity returned would be from the other person's insert statement.

SCOPE_IDENTITY() returns the last identity generated in the current scope.  A trigger runs in a different scope, so SCOPE_IDENTITY is not affected by inserts generated by triggers.  Another person's INSERT is run in a different scope, so their Identity value will not be returned.  Only the identity from the insert performed in the current scope can be returned.
0
 
HillwaaaConnect With a Mentor Commented:
Hi mwheeler_fsd,
> SET @IC = IDENT_CURRENT('tbl1')

replace with: SET @IC = @@IDENTITY

Cheers,
Hillwaaa
0
 
Raynard7Commented:
Can't you just use

SET @IC = @@IDENTITY

?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Brendt HessSenior DBACommented:
Use Scope_identity instead:

SET @IC = scope_identity()

This returns the last identity generated within the current scope.  See BoL for a more detailed description.
0
 
mwheeler_fsdAuthor Commented:
Thanks to all for such quick responses.

I had it in my head you had to SELECT @@IDENTITY before you could use it, such as:

SELECT @@IDENTITY as RecID

and did not realize you could call it directly as shown. This seems to work perfectly!

Many thanks to all.

Best,
Mike
0
 
mwheeler_fsdAuthor Commented:
After reviewing the comments from bhess1, I believe in my scenario SCOPE_IDENTITY() is the ultimate solution that will absolutely prevent the return of an incorrect ID. I need to make absolutely sure that the tbl1 ID column is the value for the tbl2 insert for the user that is doing the insert - and only that user.

Hillwaa's answer is correct as well and very much appreciated.

In this particular case, I was quick to try the first answer, it worked, so points were awarded. Given the circumstance, I'm going to ask one of the Community monitors to award additional points to bhess1.

I can't tell you how much I appreciate and value this site. The last thing I would ever want to do is be disrespectful of the Experts, or inequitable with points.

I will see what can be done.

My apologies.

Mike
0
All Courses

From novice to tech pro — start learning today.