T-SQL: @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
Posted
by Terrapin
on Stack Overflow
See other posts from Stack Overflow
or by Terrapin
Published on 2009-01-26T21:19:38Z
Indexed on
2010/04/08
10:43 UTC
Read the original article
Hit count: 695
I have seen various methods used when retrieving the value of a primary key identity field after insert.
declare @t table (
id int identity primary key,
somecol datetime default getdate()
)
insert into @t
default values
select SCOPE_IDENTITY() --returns 1
select @@IDENTITY --returns 1
Returning a table of identities following insert:
Create Table #Testing (
id int identity,
somedate datetime default getdate()
)
insert into #Testing
output inserted.*
default values
What method is proper or better? Is the OUTPUT method scope-safe?
The second code snippet was borrowed from SQL in the Wild
© Stack Overflow or respective owner
