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.

I realize this is an old question and it specifies SQL Server 2005 but since it is the first result to show up the MERGE statement available in 2008 and later should be mentioned for those looking for a solution. MERGE INTO TargetTable USING ( SELECT.... ) AS Source ON 1 = 2 WHEN NOT MATCHED THEN INSERT.... OUTPUT inserted.ID INTO TempTable ( InsertedID ) — Eric Carlson, Apr 12 '17 at 17:54
You dont need a merge for a simple insert. Merge is good for a insert/update, but overkill for a simple insert into. Andy's output answer worked for me and helped take away an index lock. — CodeMonkeyForHire, Apr 13 '17 at 14:23
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 c — clemahieu, Dec 13 '08 at 08:02
You might need a `MERGE` with `OUTPUT` if you'll be inserting related items and thus need a mapping table like (FakeID, IdentityID) to know which fakeid maps to the resulting `IDENTITY` value. — JohnnyFun, Jun 02 '21 at 18:53

score 170 · Answer 1 · answered Sep 19 '08 at 09:14

170

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.

answered Sep 19 '08 at 09:14

Andy Irving

2,657
1
14
11

7

But then how do you update table B? I mean, how do you associate every record of @output with a record in B? If you are using fname, lname as a key then is simpler to use njr's solution. – munissor Mar 16 '12 at 12:46
2

@munissor, I know this is an old thread, but check out [this simple-talk article](http://www.simple-talk.com/sql/learn-sql-server/working-with-the-insert-statement-in-sql-server/) on the subject. Look at the section "Adding an OUTPUT clause". – Mr Moose Aug 06 '12 at 12:52
2

@munissor a little late but you can do `output inserted.id, inserted.whateverColumn into @output` – Dennis Rongo Jul 22 '14 at 23:38
1

Note that this fails miserably when table A has a trigger, because a bug that Microsoft refuses to fix. Another workaround is here: https://stackoverflow.com/q/13198476/2557263 – Alejandro Nov 03 '17 at 18:47

score 5 · Answer 2 · answered Sep 18 '08 at 19:48

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.

score 1 · Answer 3 · edited Apr 18 '13 at 08:51

1

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

edited Apr 18 '13 at 08:51

Michał Powaga

22,561
8
51
62

answered Sep 18 '08 at 19:32

Cory

22,772
19
94
91

He's not trying to update table A, he's trying to update table B. Table B doesn't have an identity column. – njr101 Sep 18 '08 at 19:50

score 1 · Answer 4 · edited Apr 18 '13 at 08:52

1

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

edited Apr 18 '13 at 08:52

Michał Powaga

22,561
8
51
62

answered Sep 18 '08 at 19:51

Dmitry Khalatov

4,313
3
33
39

score 0 · Answer 5 · answered Dec 15 '20 at 12:11

-- first create a table for show how its works
CREATE TABLE [dbo].[myTable]
  (
     [id]   [INT] IDENTITY(1, 1) NOT NULL,
     [text] [VARCHAR](10) NULL
  )
ON [PRIMARY]

GO

-- var table for keep new inserted id
DECLARE @tblNewInserted TABLE
  (
     newids INT
  )

--use the output clause in insert statement
INSERT INTO [dbo].[myTable]
output      inserted.id
INTO @tblNewInserted
VALUES      ('aa'),('bb'),('cc')

SELECT *
FROM   @tblNewInserted

score 0 · Answer 6 · answered Sep 18 '08 at 19:26

0

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

answered Sep 18 '08 at 19:26

Matt

273
1
2
12

score 0 · Answer 7 · answered Sep 18 '08 at 19:29

0

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.

answered Sep 18 '08 at 19:29

Darren Kopp

76,581
9
79
93

4

Wrong, records are not guaranteed to go in to the databse in the order in which you think they are going in. – HLGEM Jan 28 '09 at 22:34

score -7 · Accepted Answer · answered Sep 18 '08 at 19:52

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.

How to insert multiple records and get the identity value?

8 Answers8

Linked

Related