0

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?

Ákos
  • 11
  • 2
  • 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 (...)` ? – Bogdan Sahlean Jun 03 '16 at 10:46

1 Answers1

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);
Ivan Starostin
  • 8,034
  • 5
  • 18
  • 35