get new SQL record ID

Question

How can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005)

Answer 1

SELECT SCOPE_IDENTITY()

using @@IDENTITY can have unexpected results, so be careful how you use that one. Triggers that insert records to other tables will cause the @@IDENTITY value to change - where SCOPE_IDENTITY() will give you the last identity from only your current scope.

Here's a sample that'll show the difference between @@IDENTITY and SCOPE_INSERT() and how they can return different values..

use tempdb
go
create table table1
    (ID int identity)
go
create table table2
    (ID int identity(100, 1))
go
create trigger temptrig 
    on table1 
    for insert
as
begin

    insert  table2 
     default values;

end
go
insert  table1 
default values;
select  SCOPE_IDENTITY(), 
        @@IDENTITY

Another option that nobody has discussed here is to use the OUTPUT clause that is in SQL 2005. In this case, you'd just have to add the output clause to your insert, and then catch that recordset from your code. This works well when inserting multiple records instead of just 1...

use tempdb
go
create table table1
    (ID int identity)
go
insert   table1 
output   inserted.ID
default values;
--OR...
insert   table1 
output   inserted.$identity
default values;

Answer 2

SELECT @@IDENTITY usually works, but could return the identity of a record inserted because of a trigger or something, and not the original.

SELECT SCOPE_IDENTITY is what I'd recommend. It returns values inserted only within the current scope.

There is also a "IDENT_CURRENT(tablename)" that returns the last identity inserted for a specific table.

Answer 3

SELECT @@Identity or SELECT SCOPE_IDENTITY() both work however Selecting SCOPE_Identity() is safer because it returns the last auto generated ID within your current scope. So for example assume we have a table called ScopeIDTable and on this table we have a trigger. This trigger will insert into a record into TriggerIdTable both tables have an auto increment column.

If you use SELECT @@Identity you will get the last auto increment in that session which would be the Id generated from within the trigger (TriggerIdTable).

If you use SELECT SCOPE_IDENTITY() you will get the id from your ScopeIdTable.

Answer 4

There are three ways to get the the last identity in sql.

They were already mentioned by others, but for completeness:

• @@IDENTITY - can also return ids created in other objects in the same scope (think triggers)
• IDENT_CURRENT - limited to a table, but not to your scope, so it can give bad results for busy tables
• Scope_Idenity() - Limited to the scope of the request. Use this 99% of the time

Additionally, there are three ways to take that ID and return it to your client code:

• Use an output parameter in a stored procedure
• Use a return value.

INSERT INTO [MyTable] ([col1],[col2],[col3]) VALUES (1,2,3); Return Scope_Identity()

• Select the id into a result set. For example, your sql statement would look something like this:

  
  Dim ResultID As Integer
  Dim strSQL As String
  strSQL = "INSERT INTO [MyTable] ([col1],[col2],[col3]) VALUES (1,2,3); SELECT Scope_Identity()"
  rsResults.Open strSQL, oConn
  ResultID = rsResults("ID")
  

Unfortunately (or fortunately, from my point of view) my Classic ASP it too far gone to show examples of the first two.

Answer 5

You run the query

select scope_identity()

using the same database connection, before doing anything else with it. The result is, as you probably expect, a record set containing a single row that has a single field. You can access the field using index 0, or you can give it a name if you prefer that:

select scope_identity() as lastId

Answer 6

Where multiple records need to inserted at once in a set-based fashion, it can get more interesting.

I have sometimes used GUIDs generated clientside (but for classic ASP you'd probably need to use a utility to generate the values) or, more often, a NEWSQUENTIALID() constraint on the GUID key column at the server end.

I'm aware not everyone like GIUDS though, for some quite valid reasons (their size and how it affects indexing/paging for one).

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=50164&pg=2

Answer 7

I always wondered why one would ever want to use

@@identity

since

select scope_identity()

obviously is the most save way to accomplish what Scot is asking for.

Answer 8

thanks all who suggested SELECT SCOPE_IDENTITY(). I was able to create a stored procedure:

USE [dbname]
GO 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsert]
(
   @Nn varchar(30)
)
AS
BEGIN TRANSACTION InsertRecord 
   INSERT INTO A (Nn) 
   VALUES (@Nn) 
   SELECT NewID = SCOPE_IDENTITY()   -- returns the new record ID of this transaction
   COMMIT TRANSACTION InsertRecord

and call the sproc using VB:

Dim strNn ' var to be passed
Set cn = Server.CreateObject("ADODB.Connection") 
connectString = "DSN" 
cn.Open connectString, "user", "PW0rd" 
Set rs = Server.CreateObject("ADODB.Recordset") 
set rs = cn.Execute("EXEC [dbname].[dbo].[A] @Nn=" & strNn)
'return the value
resultID = rs(0)

I can now use resultID anytime I refer to the newly created ID