Solved

Transaction using @@IDENTITY or IDENT_CURRENT() Clarification

Posted on 2006-11-29
7
946 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
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:bhess1
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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:
bhess1 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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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