Solved

Transaction using @@IDENTITY or IDENT_CURRENT() Clarification

Posted on 2006-11-29
7
948 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: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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server connect issues 4 39
Syntax issue with my Where Clause SQL 2012 20 38
SCOM to SQL port 14xx failed? 1 31
SQL Select Query help 1 37
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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

749 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