Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
519 views
in Technique[技术] by (71.8m points)

sql server - get new SQL record ID

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:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...