How to insert multiple records and get the identity value?

Solution 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.

Solution 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.

Solution 3:

As far as I understand it the issue you are having is that you want to INSERT into Table A, which has an identity column, and you want to preserve the identity from Table B which does not.

In order to do that you should just have to turn on identity insert on table A. This will allow you to define your ID's on insert and as long as they don't conflict, you should be fine. Then you can just do:

Insert into A(identity, fname, lname) SELECT newid, fname, lname FROM B

Not sure what DB you are using but for sql server the command to turn on identity insert would be:

set identity_insert A on