How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

SQL Server

• With SQL Server 2000, there are a couple of new functions that are better than @@IDENTITY. Both of these functions are not global to the connection, which is an important weak point of @@IDENTITY. After doing an insert, you can call: 
   
  

  PRINT IDENT_CURRENT('table')

   
  This will give the most recent IDENTITY value for 'table' - regardless of whether you created it or not (this overrides the connection limitation of @@IDENTITY -- which can be useful). 
   
  Another thing you can do is: 
   
  

  PRINT SCOPE_IDENTITY()

   
  This will give the IDENTITY value last created within the current stored procedure, trigger, etc.  
   
  If you using a version of SQL Server prior to 2000 (or you are in compatibility mode < 80), the best way is to use a single stored procedure that handles both the INSERT and the IDENTITY retrieval using @@IDENTITY. 
   
  Here is sample code for the stored procedure: 
   
  

  CREATE PROCEDURE myProc      @param1 INT  AS  BEGIN      SET NOCOUNT ON      INSERT INTO someTable      (          intColumn      )      VALUES      (          @param1      )      SELECT NEWID = SCOPE_IDENTITY()  END

   
  And you would call this from ASP as follows: 
   
  

  <%      fakeValue = 5      set conn = CreateObject("ADODB.Connection")      conn.open "<conn string>"      set rs = conn.execute("EXEC myProc @param1=" & fakeValue)      response.write "New ID was " & rs(0)      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

   
  If you are using SQL Server 7.0, simply change the line in the stored procedure from ... 
   
  

  SELECT NEWID = SCOPE_IDENTITY()

   
  ... to ... 
   
  

  SELECT NEWID = @@IDENTITY

   
  The reason SCOPE_IDENTITY() is preferred over @@IDENTITY is that if you perform an INSERT, and that table has an INSERT TRIGGER which then, in turn, inserts into another table with an IDENTITY column, @@IDENTITY is populated with the second table's IDENTITY value. So, if you are stuck using SQL Server 7.0 and need a workaround to retrieving the @@IDENTITY value because you have a trigger that also inserts into another IDENTITY-bound table, you're in luck. You can add this code to the first line of the trigger, but you will have to update all of your application and stored procedure code to deal with this new SELECT: 
   
  

  CREATE TRIGGER triggerInsert_tablename ON tablename FOR INSERT AS   BEGIN      SELECT @@IDENTITY      -- rest of trigger's logic...  END  GO

   
  With that said, there are also potential cases where SCOPE_IDENTITY() can fail, but I think this possibility is more remote than with @@IDENTITY. Observe this repro, provided by David Portas: 
   
  

  CREATE TABLE Table1  (      i INTEGER IDENTITY(1,1) PRIMARY KEY,      x INTEGER NOT NULL UNIQUE  )  GO    CREATE TRIGGER trg_Table1 ON Table1  INSTEAD OF INSERT  AS  BEGIN      SET NOCOUNT ON      INSERT INTO Table1 (x)      SELECT x FROM Inserted  END  GO    INSERT INTO Table1 (x) VALUES (1)  GO    SELECT SCOPE_IDENTITY(), IDENT_CURRENT('Table1')

   
  Result: 
   
  

  ------ ------  NULL   1

   
  This is because the actual INSERT happened outside of the scope of the caller, so SCOPE_IDENTITY() was not populated there. I have requested that the documentation for SCOPE_IDENTITY() be updated to reflect the above scenario.

Access

• Jet/OLEDB provider now supports @@IDENTITY! See KB #232144 for more info, and see Article #2126 to ensure you are using a Jet/OLEDB connection string. 
   
  So with that new information, here is the technique for obtaining this value using Access: 
   
  

  <%      fakeValue = 5      set conn = CreateObject("ADODB.Connection")      conn.open "<conn string>"      sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _          VBCrLf & " SELECT @@IDENTITY"      set rs = conn.execute(sql)      response.write "New ID was " & rs(0)      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

   
  If you are unable to use JET 4.0, you can do a more risky hack like this: 
   
  

  <%      fakeValue = 5      set conn = CreateObject("ADODB.Connection")      conn.open "<conn string>"      conn.execute "INSERT someTable(IntColumn) values(" & fakeValue & ")"      set rs = conn.execute("select MAX(ID) from someTable")      response.write "New ID was " & rs(0)      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

   
  This is more risky because it is remotely possible for two people to "cross" inserts, and receive the wrong autonumber value back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering SQL Server (see Article #2182). However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement): 
   
  

  <%      fakeValue = 5      set conn = CreateObject("ADODB.Connection")      conn.open "<conn string>"      set rs = CreateObject("ADODB.Recordset")      rs.open "SELECT [intColumn] from someTable where 1=0", conn, 1, 3      rs.AddNew      rs("intColumn") = fakeValue      rs.update      response.write "New ID was " & rs("id")      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

   
  

You can also check out KB #221931, which has an officially endorsed code sample for retrieving the AUTOINCREMENT value from an Access database.

Related Articles