SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

219 thoughts on “SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

    • CREATE TABLE Nbrs(n INT NOT NULL IDENTITY)
      GO
      SET NOCOUNT ON
      INSERT Nbrs DEFAULT VALUES
      WHILE SCOPE_IDENTITY() < 500
      INSERT dbo.Nbrs DEFAULT VALUES

      The above insert statement is not working in SQL server compact edition

      Please help me to execute this query in sql server compact edition.

  1. actually i hv to find out max value of one pramary key field.it will always a last inserted value.

    so if my connection is opened for one server..then i am not able to find out the last inserted value with scope_identity() for that server.

    so for this is there any other way .. or we have to use “select MAX(ID)”

  2. Is there any way to retrieve the latest value of a field inserted in a table (apart from primary key field)…….if yes plz let me know…..

  3. Can i use the IDENT_CURRENT to get the last record updated? I am trying to implement logging on a table with a trigger but I need to get the ID of the record that was updated.

      • Actually, if you have a stored procedure or function defined in the remote server and it executes scope_identity, it WILL work properly. It will then be up to the function or sproc to return the value to you on your server.

        Also, it depends on what you mean by a different server. If you connect to SQL on one server, execute a command that crosses to a different SQL server, that IS a different server.

        If you connect to SQL on a different server than the one you are on, that is NOT a different server.

        You shouldn’t be doing things directly to a table on a different server anyway. That is a bad security model. Also, that can affect your permission to execute other things that aren’t allowed across servers that become available when you touch them by sproc or func. (Like locking hints.)

  4. hi,

    Is it possible to find out the list of lattest used stored procedure in sql server, say for eg the list of 10 stored procedure being used in a page.

    Is it possible to retrieve from the sysobjects ?

    • Yes using a DMV

      SELECT TOP 10
      last_execution_time ,
      (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
      FROM sys.dm_exec_query_stats
      ORDER BY last_execution_time DESC

  5. I want to get the last inserted identity value of a table in a different server.

    i want to use like :
    select ident_current(‘server2.db1.dbo.table’)

    if i give like this
    select ident_current(‘db1.dbo.table’)
    i am getting the result.

  6. I need to retrieve identity column value from the below query:

    INSERT INTO table-name(col1, col2) VALUES(val1, val2); SELECT SCOPE_IDENTITY()

    using C + MS-SQL through ODBC
    Please find the psuedo code that I have used:

    hstmt = SQLAllocHandle
    odbc_stmt = “INSERT INTO STUDENT (NAME, DIVISION) VALUES (?, ?); SELECT SCOPE_IDENTITY()”
    SqlPrepare(hstmt, odbc_stmt)
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, &std_name, …
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, &std_division, …
    SQLBindCol(hstmt, 3, SQL_C_SSHORT, &std_id, ….

    SQLExecute(hstmt)
    SQLFetch(hstmt)

    ————

    Here ID is an identity column for the student table.

    I am able to insert the row. But identity column value for the inserted row is not getting retrieved to std_id variable after SQLFetch.

    Please help me to find out the problem?

    thanks in advance….

  7. Here is the problem, I create records using the identity number from the membership. I have checked the number is the same going in the db which is MS SQL 2005 however I can not retrieve records using the identity number, I have tried everything in grid view.

    For example:

    I have a drop down box list all users on selection it poplulates the grid view control.

    However the grid view control does not populate I have even tried this manually pasting the identity number in but it returns no results. I am missing something here.

    I write in VB VS2005 MS SQL 2005

    Can you point me in the right direction to make this work? I am lost.

  8. How do I get the id of the last inserted row if I don’t have an identity column? Of course, @@identity returns NULL in this case.

    Thank you

  9. Hi all,

    I want to post the solution for my previous question (Index: 12). After digging for a lot of time, found missing piece from the code.
    The key function is SQLMoreResults. It should be called just before SQLFetch.
    The main purpose of SQLMoreResults is to find out if we have more than result set. We can always combine multiple queries in a single statement. In that scenario, we may expect multiple result sets. So to traverse from one result set to other, we require to call SQLMoreResults function.

    thanks, rajuneo

  10. INSERT INTO (SELECT Lists.ListCode ,Lists.ListName
    FROM Lists)
    VALUES (‘ListCode’,’ListName’)

    plz tell me this statement run!!!!!!!!!

  11. What can I use to get the last Identity value like @@IDENTITY but over 2 connections.

    I need to do multiple inserts and my program works with @@IDENTITY as the primary key the first time the program runs.

    When I run it a 2nd time, I get the violation of primary key constraint and the statement has terminated error.

    Please help!
    Thanks,
    Claudia

  12. To 18: Yogesh:-

    Your statement is wrong. If you want to insert data from one table into another, check this out:

    insert into table2(select table2.t2id, t2name) select * from table1

  13. Hi,
    I have a query it will be a great help for me.

    I have 3 tables

    table1
    ( T1id identity (1,1) PK, Name)

    table 2
    (t2id PK, T1id, Address)

    table3
    (t3id PK,T1id,Phone)

    Now I need to insert data like this

    begin transaction
    —multiple rows are inserted from xml insert in table 1
    for i to 100
    insert into table1 values(‘adsf’)

    next
    for j to 100
    —Similarly multiple rows are inserted from xml insert in table 2 and table 3
    — but I cannot get the t1ID which is in table 1 because it has multiple identity now after xml insert

    — so here in table2 and table 3 the t1ID cannot be retrieved from scope_Identity()
    – so we need some what like array of scope_Identity()
    next
    commit transaction

    on error
    rollback transaction

    — i think you got my point
    – can you please reply me at my mail address provided

    – Thank you in advance

    Can you please solve this problem in sql server 2005

    • This is a interesting question “Prateek”.

      How do we track a list of SCOPE_IDENTITY() values produced by a single insert statement.

      I had the same issue, did you got any solution for this ?

      • Hello Pavan,

        Log the SCOPE_IDENTITY() value in some table if you want to use that later time. You can use trigger for this.
        We can provide a better resolution if you can explain you requirement.

        Regards,
        Pinal Dave

        • Hi Pinal,

          Thanks for the response.

          Some how I am not able to reply your resposne. A message ‘discarded’ is display when I submit the comments. Is there any other way that i could post you the details

          Regards
          pavan

    • Hello Prateek,

      Instead of using two saperate FOR loops, use one to insert in table1, table2 and table3. This way you woud have SCOPE_IDENTITY() values for each insert statement to be used in table2 and table3.

      Regards,
      Pinal Dave

  14. Great article.

    One question though:

    Can we use scope_identity() for selecting the last inserted value in different server?

  15. Dear Pinal,

    Firstly I would like to thank you for this wonderful site.

    Secondly I have a question to post.

    What is the syntax to alter a table to add identity property (with seed and incremental values) to an already existing column in that table?

    • You cant alter the existing column to have identity property
      The only way is to create a new column with identity,drop old column and rename new column to old column

  16. I have been running replicated servers using merge replication and SQL 2000 for about a year without issue. I have several sp’s that used @@Identity

    I upgraded to SQL 2005 last week.

    Guess what – those SP’s are now intermittently returning the identity of a table from an associated repl trigger.

    Have you seen or heard of this occurring after an upgrade?

    Thank goodness I stumbled onto your article and was able to fix all instances of @@Identity in my SP’s. (Now I have to replicate the sp’s manually off-hours….)

    thanks
    Wes

  17. I want to select the records from a table which are updated or added in the last one month (table doesn’t have any datetime field and I can’t change the structure). Is it possible to write a sql query for that?

  18. very informative article……..thanks a lot…..

    but it will be more easy to understand if examples had been put here………

  19. Hi Viswanath,

    I dont think that you can add the Identity to the existing coloumn. You need to drop the table and recreate with Identity. You can add a new column with Identity in alter table.

    - Vijay Anand Kannan

  20. Hi, Pinal,
    The Solution you have given seems very problem solving. I will try this for sure. My problem is this that: I have an web application, problem is that; when more than one user enterd data on the same time the data entered wrongly i.e. user 1 data save in user2 and user 2 data saves in itself(user2)

  21. Iam having a real strange issue with both the @@identity and the scope_identity(). The problem is running the same insert on two different machines with the exact DBs and the result on machine is as expected and on the other i get null values for both. unlike the examples quoted by the users here, iam using a select statement instead of values list to insert.
    And Iam using SQL 2005 w/ SP2 on both the machines. (The backward compatibility levels for both are SQL server 2000 (80) if you need to know)

    Here is what Iam doing:

    INSERT INTO TEMPTABLE ( column1, column2)
    SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
    SELECT @@IDENTITY;

    INSERT INTO TEMPTABLE ( column1, column2)
    SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
    SELECT Scope_Identity();

    INSERT INTO TEMPTABLE ( column1, column2)
    SELECT t1, t2 FROMNewTable where t3 = ‘somevalue’;
    SELECT Ident_Currenty(‘TEMPTABLE’);

    One machine gives me same value on all three statements, while the other gives me null on first two and gives me a value on the third.

    Any insights will be appreciated.

    - Mahee

  22. hi

    Please help me out.

    In my stored proc, I use @ident_current of a table(Table X) as a default value for my table variables (Temp Table)

    Now Since Im creating a Table Variable, I want one of its columns to have a default value of the identity of the Table X , Now when creating this Table Variable, it will not accept
    a variable as a default hence forcing me to use Ident_Current(Table X) as the default. I know that this can produce erroneous data as it applies to any session .. is there another way out? I cant use @@Identity as there are 2 more Table Variables created after the insertion of Table X – Hence will pick up wrong identity value ….
    here’s my code
    ——————————————–

    INSERT INTO TABLE X

    DECLARE A TABLE A
    INSERT INTO TABLE A

    DECLARE TABLE B
    INSERT INTO TABLE B

    DECLARE C TABLE (COL1, COL2 INT DEFAULT IDENT_CURRENT( ‘TABLE X’ ),COL3)

    ————————————————————

  23. The main problem is, I can’t get the query result from 2 different connection with different table.
    But with the same connection/server, I can get the query result.

    SELECT SERVER1enroll.ID
    FROM SERVER1enroll
    WHERE SERVER1enroll.ID NOT IN (SELECT SERVER2enroll.ID FROM SERVER2enroll)

    And is there a shortcut for that SQLcode to make a faster query than that.

    Can anyone solve that?
    I’m using 2 different connection, Server1 on my PC, Server2 is on the other PC.

    I’m using VB9
    Can anyone share some code for development testing?

  24. Hi,

    SELECT IDENT_CURRENT(’tablename’)

    The above selects the last record from tablename.
    How do i loop through the last 10 records?

    Thanks

  25. hi, i get last id of inserted data by using SCOPE_IDENTITY() from table1

    Now my problem is how to insert the id from SCOPE_IDENTITY() to table 2

    any guide are welcome

    Thanks in advance

  26. sha,

    A safe way to do is to assign the SCOPE_IDENTITY value to a variable after the first INSERT statement. For example:

    DECLARE @id INT
    INSERT INTO firstTable(col1) SELECT 1
    SELECT @id = SCOPE_IDENTITY()

    INSERT INTO secondTable(col1, id) SELECT 1,@id

  27. “Helvin

    Hi,

    SELECT IDENT_CURRENT(’tablename’)

    The above selects the last record from tablename.
    How do i loop through the last 10 records?

    Thanks”

    Helvin,
    Probably the easiest option is to select the last 10 records. For example:

    SELECT TOP 10 col1, col2, colN
    FROM tablename
    ORDER BY IdentityColumn

  28. thanks, nice one.
    I am doing T-SQL perfomance analysis. In my case I have no issue in using eigther of this, Can anyone tell me that which one faster @@identity or SCOPE_IDENTITY() ??

  29. Hi pinale
    i’m unable to access db it display’s timedout !

    is any problem with deadlock or network as i checked by using trace files but i could not find any error msg on deadlocks.

    give me your valuabl sugesstion.

    Regards,
    Harinath

  30. @Harinath,

    Were you able to log into SQL Server and you cannot access database ?

    Please post exact error message.

    What is state of database, is it online /suspect/restoring ?

    Regards,
    IM

  31. Pingback: SQL SERVER - 2008 - SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution Journey to SQL Authority with Pinal Dave

  32. Hi,

    I need to know, when i should use @@identity or scope_identity() inside any scope. What criteria i should keep in mind when i have to use any one of them in my program.

    Thanks

    Anil kumar dubey

  33. i have used

    SELECT IDENT_CURRENT(dbo.ITA_STORICO_ORDINI_DISTINTA)

    but i have received this error

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier “dbo.ITA_STORICO_ORDINI_DISTINTA” could not be bound.

    ??????

  34. SELECT CONVERT(INT, SCOPE_IDENTITY()) AS ID

    will return the scope identity in for OdbcCmd object. Just doing a SELECT SCOPE_IDENTITY() results type cast errors if you are trying to get the ID out like this

    ID = (int) cmd.ExecuteScalar();

  35. @Mauro

    Table name should be in single quotations like this,

    SELECT IDENT_CURRENT(‘dbo.ITA_STORICO_ORDINI_DISTINTA’)

    Try this, Should work.

    IM.

  36. hello ..
    i am trying to make a sp how looks something like this,
    what i want is to be able to get 2tables to create a new customer. .. i dosent work.. can you help me???

    @Namn varchar(40),
    @Adress varchar(50),
    @Postnr varchar(6),
    @Beskrivning varchar(max),
    @Erbjudande varchar(max),
    @kontaktUppgift varchar(30),
    @CafeId int

    AS
    BEGIN
    SET NOCOUNT ON;

    BEGIN TRAN
    declare @CafeId int
    BEGIN TRY

    INSERT INTO app.Cafe(Namn, Adress, PostNr, Beskrivning, Erbjudande)
    VALUES(@Namn, @Adress,@Postnr,@Beskrivning,@Erbjudande)

    Insert into app.Kontakt ( kontaktUppgift , CafeID)
    values( @kontaktUppgift,@CafeId)

    SET @CafeId = SCOPE_IDENTITY();

    COMMIT TRAN
    END TRY

    BEGIN CATCH
    –Återställer till innan begin tran
    ROLLBACK TRAN
    RAISERROR(‘fel i överföringen.’ ,16,1)
    END CATCH
    END

  37. @Dnyaneshwar

    What is not working about @@IDENTITY? Please give an example of what you are trying, and what you expected.

  38. None of the above is compatible with HSQLDB.
    What if we want to junit the MSSQL DAOs using HSQLDB in memory DB?

    How can we make the query compatible with both SQLServer and HSQLDB??

  39. I have problem regarding this three

    I have created sp for insert values in to three tables. Those three tables are like steps 1st table’s primary key keeps second table and second table primary key keeps the third table. Please follow following example to more clarification
    insert Table 1 set ID1=SCOPE_IDENTITY()
    Cursor for table 2
    Insert table2 (foreign key ID1 ) SET ID2= SCOPE_IDENTITY()
    Cursor for table 3
    Insert table3 (foreign key ID3 ) SET ID2= SCOPE_IDENTITY()
    END CURESOR 3
    END CURESOR 2

    Can I used Scope_Identity() for this scenario or any other solutions are welcome. Thanks for your advance reply.

  40. Prateek:
    Do a loop inside every insert. In that way, you’ll be able to retrieve SCOPE_IDENTITY() after each insert

    loop:
    begin
    Insert 1
    loop:
    Insert2
    loop:
    begin
    Insert3
    end
    begin
    end
    end

  41. Hello All,
    I wish to fetch last record from a table in sql server 2005 without using any group by or stored procedure is it possible

  42. @Sachin Kalra
    If proc A calls B and B calls C – Everything in all the 3 procedures are in the same session.

    However, the SCOPE of a piece of code in PROC C will be limited only to that procedure (C). But it will be in the same session as A and B

  43. Following SQL Query is to get the LAST INSERTED ID if the column is auto increment using iBatis and SQL SERVER.

    – your insert statement

    select @@IDENTITY as value

  44. I think it’s important to note here that while @@IDENTITY and SCOPE_IDENTITY() appear to work the same in the context of the same scope, that only @@IDENTITY will work if you happen to be trying to reference the current identity value within the same insert statement that is creating the IDENTITY value. SCOPE_IDENTITY() will return a NULL value in this case. I know you will ask why you would ever want to do this, but i just had a case where i had to do this and SCOPE_IDENTITY() did not work.

  45. I’m convinced with the use of SCOPE_IDENTITY until one scenario i found out that SCOPE_IDENTITY doesn’t return the desired value if Instead of Triggers is implemented on the desired table.

    But luckily, the @@IDENTITY works with that way.

    However, still can’t understand the logic behind the scenario.

    (Scenario Notes:)

    INSTEAD OF TRIGGERS => checks the other tables in terms of required data, then, reinserts all fields with updated data.

  46. Hello,

    I have the same question like raksha posted
    on July 9, 2007 at 2:07 am

    Can you please help us?

    Thanks and Regards,
    Sandhya P.

  47. Good explanations.

    In one of my application SCOPE_IDENTITY() returns 1, some times. Below is the Code

    INSERT INTO CUSTOMER(…)
    SELECT … FROM #temp_table

    SET @id = SCOPE_IDENTITY()

    Note: Here CUSTOMER table has a AFTER trigger.

    Any Guess..?

  48. SCOPE_IDENTITY() is useful when multiple user inserting data in same table at same time??
    so that will get separate identity for each row inserted.
    ??
    or will get mess up

  49. Thanks a lot. Thearticle was very useful. Keep writing such articles as it is very useful for Developer community…

  50. Hi,

    I am inserting multiple record. i am getting only last record id.i need latest inserted record id.
    SCOPE_IDENTITY() it’s givining only 1 record.

    example: I inserted 3 record,i need 3 records id.

    Create PROCEDURE [dbo].[OrganisationSelectedType_insert]
    (
    @prmOrgTypeID nvarchar(50),
    @prmOrganisationID nvarchar(50),
    @prmOrganisationName nvarchar(50)

    )
    AS
    BEGIN
    INSERT INTO [OrganisationTypes] ([OrganisationID],[OrganisationName],[OrgTypeID])
    SELECT @prmOrganisationID,@prmOrganisationName, data FROM [Split] (@prmOrgTypeID, ‘;’)

    SELECT SCOPE_IDENTITY() As savedId
    END

    please help.

    Thanks

  51. I have a table of 25 records. Client mistakenly deleted the 15th row. Can u able to insert the same row with same identity? If possible how?

  52. I have query like below

    declare @str_CustomerID int
    Insert into IMDECONP38.[Customer].dbo.CustomerMaster
    ( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
    values ( ‘werw12e’ , ‘jkj12kj’ , ’3212423sdf’ , ’1212121′
    )

    select @str_CustomerID= scope_identity()

    after execution it returns null in my parameter

  53. As we get in the insertion , can’t we get the id (primary key) of a table when it’s updated??
    I only need to get the last updated primary key. Thanx

  54. Hi Pinal,
    i am adding multiple rows in a table using XML. How do i get @@identity of each row inserted?
    Please help……
    Also please tell me is is feasible or not.

  55. Hi! can anyone help me..

    Im creating a CAI (computer aided instruction) on VB6
    and i just want to view a last record of ID and increment it by 1? can anyone please help me..?

    Thanks a lot… :D

  56. hi,
    can you answer the query for this:

    The database scheme consists of four tables:
    Product(maker, model, type)
    PC(code, model, speed, ram, hd, cd, price)
    Laptop(code, model, speed, ram, hd, screen, price)
    Printer(code, model, color, type, price)
    The table “Product” includes information about the maker, model number, and type (‘PC’, ‘Laptop’, or ‘Printer’). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table “PC” is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM – ram (in Mb), hard disk drive capacity – hd (in Gb), CD ROM speed – cd (for example, ’4x’), and the price. The table “Laptop” is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size – screen (in inches). For each printer in the table “Printer” it is told whether the printer is color or not (color attribute is ‘y’ for color printers; otherwise it is ‘n’), printer type (laser, jet, or matrix), and the price.

    * Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).
    Result set: model with higher number, model with lower number, speed, and RAM

  57. hi
    i have a doubt pls clarify it….
    i created a table with an identity column say a student table , student id as IDENTITY(100,1)… so at each insert the student id will be incremented by 1 starting from 100 and goes on 101,102……….

    actually what i need is the student id should be like this “S100,S101,S102″…. is this possible using IDENTITY….

    how can i get this …..

    thanks in advance…

    Deepan

  58. Hi Pinal,
    Your article is just awesome.
    I have a small query regarding the performance of the Three Clauses used above:
    Can you please tell which one is better from performance point of view

    @@IDENTITY / SCOPE_IDENTITY() / IDENT_CURRENT(‘tablename’)

    Waiting for your reply.

    Regards
    Gaurav Girdher

  59. Hi Dave,

    i m new to SQL server, i m stuck in the problem, i have to find max id based on department

    eg:

    e_id e_dept
    as123mk MKT
    as124mk MKT
    as125it IT
    as126it IT

    i m having 2 column e_id and e_dept which store employee id and employee department
    in above table i have to find max id in MKT dept that is 124 and max id in IT dept that is 126.

    Thanks in advance
    waiting for reply

  60. Hi
    I AM UDAY

    I AM INSERTING BULK DATA USING XML IN SQL SERVER 2005.i AM USING C#.NET.

    fOR SINGLE TABLE IT WORKS NICE BUT MY PROBLEM IS.I AM INSERTING IN TWO TABLES WITH RELATIONSHIP.MY TABLES ARE COMPANY AND EMAIL.

    AND I AM TRYING TO SAVE COMPANY ID IN EMAIL TABLE.BUT IT RETURN ONLY LAST ID WHEN I AM INSERTING USING XML.HOW I CAN FIND @@IDENTITY FOR EACH ROW DURING XML INSERT.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROCEDURE [dbo].[insertDataUsngXml]
    @XML_TRANSACTIONDETAILS XML

    as
    DECLARE @i_Doc INT,
    @DataID int

    BEGIN transaction
    EXEC SP_XML_PREPAREDOCUMENT @i_Doc OUTPUT,@XML_TRANSACTIONDETAILS
    INSERT INTO tempdatainfo
    (
    companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog
    )
    SELECT
    companyname,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog

    FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)

    WITH
    (
    companyname varchar(100),
    website varchar(100),
    country varchar(100),
    contactperson varchar(100),
    telphone varchar(100),
    mobile varchar(100),
    fax varchar(100),
    region varchar(100),
    status varchar(100),
    username varchar(100),
    date datetime,
    category varchar(100),
    urlorcatalog varchar(100),
    email varchar(100),
    email1 varchar(100)

    )

    SELECT @DataID=@@IDENTITY

    IF @@Error0 GOTO PROBLEM

    insert into tempemailinfo
    (
    DataID,email,category,status,region,username,date

    )
    SELECT
    @DataID, email,category,status,region,username,date

    FROM OPENXML(@i_Doc,N’master/TRANSACTION’,2)

    WITH
    (

    status varchar(100),
    username varchar(100),
    date datetime,
    category varchar(100),
    urlorcatalog varchar(100),
    email varchar(100),
    email1 varchar(100),
    region varchar(100)

    )

    IF @@Error 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    ROLLBACK TRANSACTION
    RETURN 1

    THANKS.

  61. ALTER PROCEDURE [dbo].[REGISTER_USER]
    @username varchar(50),
    @password varchar(50),
    @bitfield bit,
    @fname varchar(50),
    @lname varchar(50)
    AS
    declare @role as varchar(50)
    declare @fk int
    BEGIN

    if(@bitfield = ’1′)
    begin
    SET @role=’Admin’;
    insert into USER_INFO (UserName, Password, Role)values(@username, @password, @role);
    select @fk = USER_ID from USER_INFO where UserName=@username AND Password=@password;
    insert into ADMIN_DETAILS (User_id,Fname, Lname) values(@fk, @fname, @lname);
    select SCOPE_IDENTITY();
    select @@IDENTITY
    end
    if(@bitfield = ’0′)
    begin
    SET @role=’Guest’;
    insert into USER_INFO (UserName, Password, Role)values(@username, @password, @role);
    select @fk = USER_ID from USER_INFO where UserName=@username AND Password=@password;
    insert into GUEST_DETAILS (User_id,Fname, Lname) values(@fk, @fname, @lname);
    select SCOPE_IDENTITY();
    select @@IDENTITY
    end

    — Insert statements for procedure here

    END

    Here I want to return the scope identity of both USER_INFO and GUEST_DETAIL tables at a time.

    How is it possible by using the scope_identity() function.

  62. hi Pinal,
    In the case of Uniqueidentifier ,returning empty from stored procedure while i am using @@IDENTITY or SCOPE_IDENTITY() for getting the primary key (which is unique identifier)of last inserted row.
    Can you solve this problem?
    Any other function available for retrieving the primary key value of last inserted row while using the uniqueidentifier type for primary key?

  63. I’m having trouble figuring out how to gather multiple ids from an insert into from select. I need to create a new revision of an item and the item is multirelated to another table and that table is multi-related to other tables, I need to gather the new ids and relate them to the old ids so I can make sure all the relationships are connecting correctly. Doing it 1 at a time would work with a cursor but I’m trying avoid using a cursors. I would apriciate any help. Thank You

  64. Pingback: SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31 « SQL Server Journey with SQL Authority

  65. Note that in help, it documents that rollback will remove the record, but not the identity change produced by the attempted insert. I’ve also verified all three identity methods return the same answer after a rollback. (Since my tests didn’t involve a trigger.)

    Note also that IDENT_CURRENT will only work on the last record inserted on THIS server if the table is merge replicated. Hmmm, I’ve never tried ‘srvr.dbNm.dbo.tbl’ before. I could be wrong about that.

  66. Hi pinal,

    How to change identity column values (Update) ?

    I have a MS SQL 2005 database with a table Test with column ID. ID is a identity column. I have rows in this table and all of them have their corresponding ID autoincremented value.

    ID Name
    1 abc
    2 def
    3 ghi
    4 jkl

    Now I would like to change every ID in this table like this:
    ID Name
    5001 abc
    5002 def
    3 ghi
    4 jkl

    But when I do this I get error:

    Cannot update identity column ‘ID’.
    I’ve tried with:

    ALTER TABLE Test NOCHECK CONSTRAINT ALL
    set identity_insert ID OFF/ON
    But this does not solve this problem.

  67. Hi Pinal,
    I have 1 doubt.Pls clarify me. I created 1 table.In that i am inserting 5 records at a time using bulk insert in xml.I want the record status as 1 should display for those 5 records repeatedly and for next insertion it should display as 2 for next 5 records. Is it possible. pls help me with an example.

  68. Hi Pinal,

    CREATE TABLE MyTable2
    (
    MyPK int identity,
    MyColumn1 NVARCHAR(100),
    MyColumn2 NVARCHAR(100)
    )

    INSERT INTO MyTable2
    (MyColumn1,MyColumn2)
    (select ‘A’ , ‘B’ from MyTable1)

    Here I’m using select statement to insert records to MyTable2. MyTable1 contains say, 100 records.

    now i need to use above to insert a GUID to MyTable2.MyColumn2 by combining MyPk (Current Value).

    something like bellow

    INSERT INTO MyTable2
    (MyColumn1,MyColumn2)
    (select ‘A’ , ‘B’+MyPk from MyTable1)

    How can I do this? Inserting records should be from a select statement.

  69. MyPK is the identity value of the MyTable2. it does not contain in the MyTable1. here i need current inserting row identity. just removing braces gives Invalid column name ‘MyPK’

  70. There is an important difference between IDENT_CURRENT versus SCOPE_IDENTITYand @@IDENTITY when referencing the IDENTITY value within an INSERT statement. See below for an example

    CREATE TABLE [dbo].[t_TestIdentity](
    [TestID] [bigint] IDENTITY(1,1) NOT NULL,
    [TestIDCopy] [bigint] NULL,
    [TestText] [nvarchar](32) NULL,
    CONSTRAINT [PK_TestID] PRIMARY KEY CLUSTERED
    ([TestID] ASC)
    )

    GO

    INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (IDENT_CURRENT(‘[dbo].[t_TestIdentity]‘), ‘Test1′)
    INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (SCOPE_IDENTITY(), ‘Test2′)
    INSERT [dbo].[t_TestIdentity] ([TestIDCopy], [TestText]) VALUES (@@IDENTITY, ‘Test3′)

    SELECT * FROM [dbo].[t_TestIdentity]

    TestID TestIDCopy TestText
    1 1 Test1
    2 1 Test2
    3 2 Test3

  71. i have a table with around 10,000 records. one of the column in the table is having identity. i have only deleted, but not truncated all the records in the table to keep my identity value. without having any records in the table, i need to find out my latest identity value. Can you let me know how to find the latest identity value without having any records in the table. Please update your comments on this?

  72. Hello, I am using a MS Access front end that uses ODBC to connect to a MS SQL Server 2005 with replicated tables.
    Some times I had to remove and add again the merge database for identity problems.
    there is any workarround to change the @@identity for scope_identity() in default to avoid the problems ??
    I am not writing the SQL code, then I cant chose one or the other.
    Inserting records in a vincled form is showing a register diferent than the inserted into de database and is producing errors.

  73. Hi Pinal,
    Thanks a lot for wonderful site.

    This is related to @@Identity, I want get latest value inserted in table.
    But as per this thread @@identity does not give latest value inserted in table

    While searching on other sites i got exact and i am sure correct solution, below is the link

    http://weblogs.asp.net/esanchez/archive/2006/04/24/443757.aspx#7180719

    Solution 1:

    CREATE TABLE HumanResources.Employees(
    Id int IDENTITY(1,1) NOT NULL,
    — Other columns ,
    CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (Id ASC)
    )
    ———————————————————————

    DECLARE @InsertedRows AS TABLE (Id int)
    DECLARE @NewId AS INT

    INSERT INTO HumanResources.Employees
    ( /* column names */)
    OUTPUT Inserted.Id INTO @InsertedRows
    VALUES (/* column values */)

    SELECT @NewId = Id FROM @InsertedRows

    Solution 2:
    This might helpful..
    ex:

    Create
    ( Id int IDENTITY(1,1) NOT NULL,
    colname varchar(20)
    )

    Begin Tran

    INSERT into (ColName) values (xxxxxx)

    Select max(id) from

    End

    Solution 2 can work for small kind of data.

    Thanks

  74. In continuation with above thread..
    @@identity give correct value when i run some on code on SQL editor, but when i did same thing on existing Stored procedure then it give different value.
    This is because @@Identity returns the last identity value inserted in any table or session of the database and so in a high (or even medium) concurrency environment @Identity will give you unexpected values

    Thanks

  75. good article, every time ur only the best. If i get doubt regarding sql then i do search question+by ur name only Iam great fan of u sir. Thank you.

  76. Thank you again. I’ve just bought 2 of your books. As always, you were the go to guy. One addition to this for anyone who doesn’t know. To set the return value to an output parameter do this:
    set @NameOfYourIDParamter = (select SCOPE_IDENTITY())–return new row ID

  77. Hi Pinal,
    I have one question.
    i want to insert identity column into next table.
    table1(col1(identity) pk, name)
    table2(childname,col1 fk)

    in a sinlge transaction stored procedure i want to insert two one records in both the table . like one record in table 1 and get the seed number and insert into second table and i want to return the seed value. so that i can catch it using output paramater direction from .net

    like
    declare @seedvalue bigint
    insert table1(name)values(‘basker’)
    set @seedvalue=@@identity
    insert table(childname,col1)values(‘baskerchild’,@seedvalue)
    return @seedvalue

    is this correct?

    can i use DML after setting @@identity seed value to any variable ?

    please reply

    thanks in advance

  78. I am passing table name as parameter in my stored procedure and I want to get that table’s last inserted row’s Id. How can I do that? I want to get last row’s id through SELECT IDENT_CURRENT

  79. I am using stored procedure and I am passing table name as parameter. SO in this case how could I use SELECT IDENT_CURRENT(‘tablename’)?

  80. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  81. Hi,
    In the para for SELECT SCOPE_IDENTITY() you mention below text at the end of the para.

    In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

    Can User defined function create any identity ? If yes please let me know how. I think UDF can’t perform any DML operation.

  82. We have a table with columns source_id(primary key) and root_source_id which is NOT NULL. We would like root_source_id to have a default value of source_id.
    @@identity and SCOPE_IDENTITY() give us erroneous ID values from other table, as our DB is usually accessed by a farm of 80 machines together on different tables. Using DB.IDENT_CURRENT(‘tablename’) seemed to best so far for us as we can specify the table. But even this has failed sometimes as it may still fetch IDs from other sessions. Do let me know if trigger is the only way (does not really work for us because we have to make root_source_id as nullable).

    I wonder why it is so difficult to get the identity value available in a “current” insert statement?

    CREATE TABLE dbo. (
    [source_id] INT IDENTITY(1,1) primary key,
    [root_source_id] int NOT NULL default(IDENT_CURRENT(‘source’)),
    [source_path] nvarchar(max) NOT NULL
    );
    go
    ALTER TABLE dbo.
    ADD CONSTRAINT [source_source_FK4]
    FOREIGN KEY ([root_source_id])
    REFERENCES ([source_id]) ON UPDATE NO ACTION ON DELETE NO ACTION
    go

  83. Hello Pinal,
    Thanks for good article. I am not master in Sql Server. I am tying to understand your article, but one thing I couldn’t understand, that is “While @@IDENTITY is limited to the current session, it is not limited to the current scope.”, Here what is meaning of Current Session, and Current Scope? I am facing these words first time.

  84. Hi Dave, I need your help below, I want the @@identity to be added in a column after insert, the problem is if multiple users are calling the query, the unwanted identity value is inserted. Does adding Begin Tran helps so that the identity should be within the current transaction?

    Create table tblTransactions(dctransno pk(auto-increment), vcDescription, dcTransNoCopy)

    Insert into tblTransactions values(@vcDescription);
    set @identity = @@IDENTITY
    Update tblTransactions set dcTransNoCopy= @identity where dcTransNo = @identity;

    Thanks in advance.

  85. Hello Pinal,

    Thanks for the explanation.
    In my project, i am building a sql dataadapter insert command which combines the insert statement and the select scope_identity statement.
    When running this combined statement in sql server 2005 database, the identity column does not get updated and in sql server 2008 database, a new column – Column1 is appended and the identity value is set, the original identity column stays empty.
    Whereas, when i use ‘SELECT @@IDENTITY as [identity column name]‘ after the insert statement, in sql server 2005 the identity column gets updated and in sql server 2008, the same ‘Column1′ gets added and the identity value is set.

    could you please help me understand this behavior and advice me if i should continue with @@IDENTITY ?

    Thanks a ton.

  86. Hi, I have a concern using IDENT_CURRENT. I just created a table as:

    CREATE TABLE [dbo].[Tbl1](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](50) NULL
    ) ON [PRIMARY]

    I did not insert a single record. The table is kept blank. Now when I fired below SQL:

    SELECT IDENT_CURRENT(‘TBL1′) IDENTCURRENT

    I get the output as:

    IDENTCURRENT
    1

    Why is this happening? Is this some kind of bug in SQL Server? Can you please explain me this scenario?

    Regards,
    Tarun

  87. I think that what you wrote made a great deal of sense.
    However, what about this? what if you added a little content?

    I am not saying your content isn’t solid., but what
    if you added a post title to maybe grab people’s attention? I mean SQL SERVER
    - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record | Journey
    to SQL Authority with Pinal Dave is kinda boring.

    You could glance at Yahoo’s home page and watch how they write post titles to get viewers interested.
    You might try adding a video or a pic or two to grab readers
    interested about what you’ve got to say. Just my opinion, it could bring
    your posts a little livelier.

  88. Hy sir .. i making an LMS system , in this i want courses to show by semester wise through inner join query because the courses table is different and that table is connected to semester table through foreign key.
    so for this i created two the first store procedure one procedure is below here.
    ALTER proc [dbo].[Insert_Semister_Type]
    @S_typeName varchar(50)
    as
    insert into Semister_Type (SemisterName) values (@S_typeName)

    return

    and the second is here..
    ALTER proc [dbo].[A_Update_Courses_By_Semister_Wise_For_Examination]
    as
    SELECT 0 as Of_C_ID,’—- Please Select —-’ as CourseName
    FROM Offered_Courses_Sylabus INNER JOIN
    Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
    Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
    Courses ON Program_Sylabus.Course_id = Courses.Course_id
    union
    SELECT Ad_Teacher_In_Oferd_Course.Of_C_ID, Courses.CourseName
    FROM Offered_Courses_Sylabus INNER JOIN
    Ad_Teacher_In_Oferd_Course ON Offered_Courses_Sylabus.Of_C_ID = Ad_Teacher_In_Oferd_Course.Of_C_ID INNER JOIN
    Program_Sylabus ON Offered_Courses_Sylabus.P_S_ID = Program_Sylabus.P_S_ID INNER JOIN
    Courses ON Program_Sylabus.Course_id = Courses.Course_id
    — THIS SEMISTER ID WILL ALWAYS BE CHANGE THERE ACORDING TO CURRENT SEMISTER –
    where Semister_ID=1

    return

    here i am giving the semester value by default , what i want , i want that, when i insert the first new semester by store procedure 1 so that value should automatically assign to that 2nd procedure in which i am giving the default value.. i knew only that ,that will be done by @@identity but i don’t know how to do it.. please help me i am waiting for your response . thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s