Search code examples
sql-serverinsertoutputidentity

List of inserted ID in SQL Server

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?

Solution

  • 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);