How to insert multiple records and get the identity value?

Question

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
FROM B

I'm using MS SQL Server 2005.

Answer 1

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.

Answer 2

Reading your question carefully, you just want to update table B based on the new identity values in table A.

After the insert is finished, just run an update...

UPDATE B
SET NewID = A.ID
FROM B INNER JOIN A
     ON (B.FName = A.Fname AND B.LName = A.LName)

This assumes that the FName / LName combination can be used to key match the records between the tables. If this is not the case, you may need to add extra fields to ensure the records match correctly.

If you don't have an alternate key that allows you to match the records then it doesn't make sense at all, since the records in table B can't be distinguished from one another.