How can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005)
See Question&Answers more detail:osHow can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005)
See Question&Answers more detail:osSELECT 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;