How to insert multiple records and get the identifier value?

Question

I am inserting several records into table A from another table B. Is there a way to get the identifier value of table A for writing and writing to table b without running the 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 am using MS SQL Server 2005.

Answer 1

MBelly is right for the money. But then the trigger will always try to update table B, even if it is not required (because you also insert from table C?).

Darren is also true here, you cannot return multiple identifiers as a result set. Your parameters use a cursor and take an identifier for each row inserted or use the Darren approach to store the identifier before and after. As long as you know the identifier increment, this should work if you make sure that the table is locked for all three events.

If it were me, and there was no time critical, I would go with the cursor.

Answer 2

Use the ouput suggestion since 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 identification values ​​of all inserted rows.

Answer 3

Having carefully read your question, you just want to update table B based on the new identification values ​​in table A.

After the insert is complete, just start the update ...

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

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

If you do not have an alternative key that allows you to match records, this makes no sense, since the records in table B cannot be distinguished from each other.

Answer 4

If you always want this, you can set the AFTER INSERT trigger in TableA, which will update table B.

Answer 5

You can get by joining the line number . This is possible because, because it is an identifier, it will simply increase as you add elements that will be in the order in which you select them.

Answer 6

As far as I understand, the problem is that you want to insert INSERT into table A, in which there is an identity column, and you want to keep the identity from table B, which is not.

To do this, you just need to enable the identity insert in table A. This will allow you to define your identifier for the insert, and as long as they do not conflict, you should be fine. Then you can simply do:

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

You do not know which database you are using, but for the sql server the command to enable the insert of identifier is:

 set identity_insert A on 

Answer 7

I suggest using a uniqueidentifier type instead of an identifier. In this case, you can generate identifiers before inserting:

 update B set NewID = NEWID() insert into A(fname,lname,id) select fname,lname,NewID from B