Return the last used identity

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.
LVL 6
fizchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

robertjbarkerCommented:
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
robertjbarkerCommented:
More complete example:

declare @ident int

INSERT INTO foo VALUES('bar')

set @ident = SCOPE_IDENTITY( )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sigmaconCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sigmaconCommented:
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
robertjbarkerCommented:
@@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
Dishan FernandoSoftware Engineer / DBACommented:
Hi
Simply as this..

DECLARE @IC int
SET @IC = IDENT_CURRENT('<Table Name>')
PRINT @IC
0
sigmaconCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.