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.

Thanks

Answer 1

If you always want this behavior, you could put an AFTER INSERT trigger on TableA that will update table B.

Answer 2

You can get the by joining on the row number. This is possible because since it's an identity, it will just increment as you add items, which will be in the order that you are selecting them.

Answer 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

Answer 4

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.

Answer 5

I suggest using uniqueidentifier type instead of identity. I this case you can generate IDs before insertion:

update B set NewID = NEWID()

insert into A(fname,lname,id) select fname,lname,NewID from B

Answer 6

MBelly is right on the money - But then the trigger will always try and update table B even if that's not required (Because you're also inserting from table C?).

Darren is also correct here, you can't get multiple identities back as a result set. Your options are using a cursor and taking the identity for each row you insert, or using Darren's approach of storing the identity before and after. So long as you know the increment of the identity this should work, so long as you make sure the table is locked for all three events.

If it was me, and it wasn't time critical I'd go with a cursor.

Answer 7

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 8

Andy Irving's answer is the best.

Triggers are clumsy and don't work well for arbitrary operations on your target table, especially if your target is temporary or just an intermediate.

Darren's answer is wrong, if you're inserting a set of rows, their order in the target table isn't necessarily the same as the order of your set.

Dmitry's way is bad because it requires a loop around inserting a single row at a time which is slow performance wise, always use sets when you can.

Cory's way is bad and he explained why, "as long as they don't conflict." This is going to turn in to a Saturday night call "Hey Bob, the app is crashing with a Primary key violation"

There's no more reason to post suggestions on this, Andy nailed it.