Solved

Return the last used identity

Posted on 2004-10-28
567 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
Question by:fizch
    7 Comments
     
    LVL 6

    Expert Comment

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

    declare @ident int

    INSERT INTO foo VALUES('bar')

    set @ident = SCOPE_IDENTITY( )
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
     
    LVL 8

    Expert Comment

    by:sigmacon
    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
    @@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:dishanf
    Hi
    Simply as this..

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

    Expert Comment

    by:sigmacon
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you! INTRODUCTION As AD admins or those having to deal with AD data, you probably have had to convert a timestamp or two like last logo…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now