List of inserted ID in SQL Server

Question

I have a table variable

@temp (tmpId, c1, c2, c3, d1, d2, d3)

I want to insert rows from @temp to a table (MyTable) having identity column called ID and assign the isnerted IDs to the rows in @temp. I know that the list of inserted ids can be retrieve using OUTPUT clause.

INSERT INTO MyTABLE (c1, c2, c3)
OUTPUT INSERTED.id, INSERTED.c1, ... INTO @IDs (id, c1, ...)
SELECT (c1, c2, c3)

Dispite having @IDs table I can not assigne the inserted ids to the tmpIds because the c1, c2, c3 columns are not unique. I need the Ids to insert d1, d2, d3 columns to another table. I have only one solution in my mind. Inserting rows one by one and retrieving id with SCOPE_IDENTITY(). But I'd like to aviod using loop. Any idea how to insert these rows in one step?

Paragraphia

Do you have a primary key/unique constraint defined on c1, c2, c3 columns one @temp ? Did you used a table valued type to declare this variable of did you used following syntax DECLARE @temp TABLE (...) ? – Stymie

Answer 1

Try merge trick with linking source rows with inserted identity values:

merge MyTABLE t
using @temp s
on 1=0
when not mathed then
insert ...
output inserted.ID, s.tempID 
into @linked_ids(id1, id2);

Thumping

Answer 2

I had similar issue couple days ago and finally I used different, we can say insert from ordered select, approach. Because we did not want to use merge.

Example:

INSERT INTO MyTABLE (c1, c2, c3)
OUTPUT INSERTED.id, INSERTED.c1, ... INTO @IDs (id, c1, ...)
SELECT (c1, c2, c3) ORDER BY tmpId

So the order of INSERTED.id should be guaranteed now and directed by the order of tmpId (because of insert from ordered select ORDER BY tmpId). It means tmpId must be something like order in collection inside caller application.

It looks that works well.

PS: another approach, when you don't use merge and ordered select is extend table by something like requestId and store input id to the table and return to caller application.

Countermine