Solved

Transaction using @@IDENTITY or IDENT_CURRENT() Clarification

Posted on 2006-11-29
7
944 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:mwheeler_fsd
7 Comments
 
LVL 16

Assisted Solution

by:Hillwaaa
Hillwaaa earned 250 total points
Comment Utility
Hi mwheeler_fsd,
> SET @IC = IDENT_CURRENT('tbl1')

replace with: SET @IC = @@IDENTITY

Cheers,
Hillwaaa
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
Can't you just use

SET @IC = @@IDENTITY

?
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mwheeler_fsd
Comment Utility
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 250 total points
Comment Utility
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
 

Author Comment

by:mwheeler_fsd
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now