Solved

Transaction using @@IDENTITY or IDENT_CURRENT() Clarification

Posted on 2006-11-29
7
951 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Assisted Solution

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

replace with: SET @IC = @@IDENTITY

Cheers,
Hillwaaa
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 18041191
Can't you just use

SET @IC = @@IDENTITY

?
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 18041231
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:mwheeler_fsd
ID: 18041250
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:
Brendt Hess earned 250 total points
ID: 18041264
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
ID: 18041392
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

630 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