Retrieving Identity Values When Inserting Multiple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving Identity Values When Inserting Multiple

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-21 : 18:41:44

is it possible to Retrieve Identity Values When Inserting Multiple Rows
I have searched high and low and have not been able to find anything. is there a sort of SCOPE_IDENTITY() temp table?

Thanks

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-21 : 18:43:39

Are you possibly referring to @@IDENTITY?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-21 : 19:01:27

Here is an example of using the OUTPUT clause for that:

create table #t ( id int identity primary key clustered, x int not null)

insert into #t ( x )
output inserted.id, inserted.x
select x
from
    (
    -- test data
    select x = 1 union all
    select x = 4 union all
    select x = 7
    ) a
order by
    x

drop table #t


Results:
id          x
----------- -----------
1           1
2           4
3           7

(3 row(s) affected)

CODO ERGO SUM

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-21 : 19:10:56

dang! that is so sweet MVJ

Thanks!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources