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.
How to retrieve the identity value when inserting a record into a Microsoft SQL Server table
Summary
A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.
Why @@IDENTITY and IDENT_CURRENT should not be used
Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.
Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.
How and why to use SCOPE_IDENTITY
Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.
Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.
The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.
CREATE PROCEDURE [dbo].[InsertCases]
@CaseID int output,
@CaseName nvarchar(255),
@CaseDescription ntext AS
SET NOCOUNT ON
INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription])
VALUES (@CaseName,@CaseDescription)
SET @CaseID=SCOPE_IDENTITY()
SET NOCOUNT OFF
The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to ParameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.
Visual Basic
Dim Connection As System.Data.SqlClient.SqlConnection
Dim Command As System.Data.SqlClient.SqlCommand
Dim CaseID As Integer
Connection = New System.Data.SqlClient.SqlConnection()
Command = New System.Data.SqlClient.SqlCommand()
Command.Connection = Connection
Connection.ConnectionString = _
"Server=MySvr;Database=MyDb;Integrated Security=SSPI;"
Connection.Open()
Command.CommandText = "InsertCases"
Command.CommandType = System.Data.CommandType.StoredProcedure
Command.Parameters.Clear()
Command.Parameters.Add(New _
System.Data.SqlClient.SqlParameter("@CaseID", _
System.Data.SqlDbType.Int))
Command.Parameters.Add(New _
System.Data.SqlClient.SqlParameter("@CaseName", _
System.Data.SqlDbType.NVarChar))
Command.Parameters.Add(New _
System.Data.SqlClient.SqlParameter("@CaseDescription", _
System.Data.SqlDbType.NText))
Command.Parameters("@CaseID").Direction = _
System.Data.ParameterDirection.Output
Command.Parameters("@CaseName").Value = "New Case Name"
Command.Parameters("@CaseDescription").Value = _
"New Case Description"
Command.ExecuteNonQuery()
CaseID = System.Convert.ToInt32( _
Command.Parameters("@CaseID").Value.ToString())
Connection.Close()
C#
System.Data.SqlClient.SqlConnection Connection;
System.Data.SqlClient.SqlCommand Command;
int CaseID;
Connection = new System.Data.SqlClient.SqlConnection();
Command = new System.Data.SqlClient.SqlCommand();
Command.Connection = Connection;
Connection.ConnectionString =
"Server=MySvr;Database=MyDb;Integrated Security=SSPI;";
Connection.Open();
Command.CommandText = "InsertCases";
Command.CommandType =
System.Data.CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@CaseID",
System.Data.SqlDbType.Int));
Command.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@CaseName",
System.Data.SqlDbType.NVarChar));
Command.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@CaseDescription",
System.Data.SqlDbType.NText));
Command.Parameters["@CaseID"].Direction =
System.Data.ParameterDirection.Output;
Command.Parameters["@CaseName"].Value = "New Case Name";
Command.Parameters["@CaseDescription"].Value =
"New Case Description";
Command.ExecuteNonQuery();
CaseID = System.Convert.ToInt32(
Command.Parameters["@CaseID"].Value.ToString());
Connection.Close();
Conclusion
By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.
About the author
Mike Green is the founder of Ferrysoft, a software development company specialising in Help Desk software technology.
Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?