Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return the last used identity

Posted on 2004-10-28
7
Medium Priority
?
568 Views
Last Modified: 2012-06-21
I am looking for a way to return the identity used on an insert. For example:

INSERT INTO foo VALUES('bar')

on table with a schema like:

CREATE TABLE foo (
  myId         INT     IDENTITY(1,1) NOT NULL,
  myVal       VARCHAR(20)
)

How can I get the value of myId on the previous insert statement with out using a select statement after it? I have several people accessing the database at the same, and it is possible to have two people inserting data at the exact same time and using a select afterwards could lead to the wrong identity being returned.

Please help.
0
Comment
Question by:fizch
[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
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:robertjbarker
ID: 12441421
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
   IDENT_CURRENT('table_name')

or

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
   SCOPE_IDENTITY( )

Both descriptions above are from books on line.  Looks like in your case you want SCOPE_IDENTITY immediately after the insert.
0
 
LVL 6

Accepted Solution

by:
robertjbarker earned 2000 total points
ID: 12441430
More complete example:

declare @ident int

INSERT INTO foo VALUES('bar')

set @ident = SCOPE_IDENTITY( )
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12441674
fizch, which identity value exactly are you trying to get? If you have more then one user (session) currently performing operations (inserts) on the database, and you want to find out which one the last id was that user A inserted and which one user B inserted, then you HAVE to use @@IDENTITY. SQL Server makes sure that for user A you get the id that was created for the insert performed by/for user A and for user B you get that respective one, even if there inserts where virtually at the same time. If, however, you use any of the other two functions above, you may get another users last inserted id, and thus not the one for the transaction that came logically before. Think of a session as its own independent timeline for each user, where SQL server is going to try its best to make it look like your the only one using the server. SQL Server is going to try even harder if you wrap it in transactions - as a matter of fact, the server makes certain guarantees depending on the transaction level chosen. It's always a safe choice to wrap statements that involve @@identity in an atomic transaction.

From BOL:

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.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:sigmacon
ID: 12441682
Here's a little example that within that session will ALWAYS give you the last identity create for the statement above - no matter what other users do and when:

drop table junk
create table junk (
    num int primary key clustered identity(1,1) not null,
    data varchar(2650) not null
)
go

declare
    @lastInsertId int,
    @affectedRows int


begin transaction

    insert into junk ( data ) values ( 'some important data for which I was too busy to determine a natural key' )
    set @lastInsertId = @@identity

    print @lastInsertId

    set @affectedRows = @@rowcount

    print @affectedRows
   
    select @@identity as identityVar

    select @@rowcount as rowcountVar

commit transaction

0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 12441857
@@identity and scope_identifier() will both restrict themselves to what YOU do in the current session, so what other users do will not affect you. scope_identifier() will further restrict itself to the current scope. That means the YOU will not interfere with YOU if you want to know the identity that is generated in the insert immediately before the call to scope_identfier(). How could you interfere with yourself? With a trigger. If you have a trigger that inserts another record into the table in question, @@identity will give you the identity created in the trigger, not the one you generated with the insert statement that caused the trigger.

In the following example two identities are generated. One is from the original insert, given by scope_identity(). The other is from the trigger, given by @@identity.

If you want a key to the insert you explicitely did, instead of records from various triggers, scope_identity() is the safe way to go.

drop table junk
create table junk (
    num int primary key clustered identity(1,1) not null,
    data varchar(2650) not null
)
go

CREATE TRIGGER [additionalinsert] ON [dbo].[junk]
FOR INSERT
AS
insert into junk ( data ) values ( 'some more')
GO

declare
    @lastInsertId int,
    @affectedRows int,
    @scopeInsertId int

begin transaction

    insert into junk ( data ) values ( 'some important data for which I was too busy to determine a natural key' )
    set @lastInsertId = @@identity
    set @scopeInsertId = scope_identity()

    print @scopeInsertId
    print @lastInsertId
 
commit transaction

select * from junk


0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 12442559
Hi
Simply as this..

DECLARE @IC int
SET @IC = IDENT_CURRENT('<Table Name>')
PRINT @IC
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12443678
robertjbarker is correct. SCOPE_IDENTITY is the best way to do that. I shouldn't answer questions late at night, sorry about that - no offense meant Robert ;-) Everybody, please ignore my incorrect comment above. Don't use IDENT_CURRENT, though.

From BOL:

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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 …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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