Sql – How to insert multiple records and get the identity value


I'm inserting multiple records into a table A from another table B. Is there a way to get the identity value of table A record and update table b record with out doing a cursor?

Create Table A
(id int identity,
Fname nvarchar(50),
Lname nvarchar(50))

Create Table B
(Fname nvarchar(50),
Lname nvarchar(50),
NewId int)

Insert into A(fname, lname)
SELECT fname, lname

I'm using MS SQL Server 2005.

Best Solution

Use the ouput clause from 2005:

DECLARE @output TABLE (id int)

Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B

select * from @output

now your table variable has the identity values of all the rows you insert.