TSQL Bulk insert data while returning created id's to original table

Question

I have two tables. One called @tempImportedData, another called @tempEngine. I have data in @tempImportedData I would like to put this data into @tempEngine, once inserted into @tempEngine an id gets created. I would like that id to be placed back into @tempImportedData in the corresponding row. I believe this this the purpose of OUTPUT statement. I almost have a working copy please see below.

Declare @tempEngine as table(
     id int identity(4,1) not null
    ,c1 int
    ,c2 int
);
Declare @tempImportedData as table(
     c1 int
    ,c2 int
    ,engine_id int
);
insert into @tempImportedData  (c1, c2)
    select 1,1
    union all select 1,2
    union all select 1,3
    union all select 1,4
    union all select 2,1
    union all select 2,2
    union all select 2,3
    union all select 2,4
;
INSERT INTO @tempEngine ( c1, c2 ) 
    --OUTPUT INSERTED.c1, INSERTED.c2, INSERTED.id  INTO @tempImportedData (c1, c2, engine_id) --dups with full data
    --OUTPUT INSERTED.id  INTO @tempImportedData (engine_id) -- new rows with wanted data, but nulls for rest
    SELECT 
         c1
        ,c2
    FROM 
        @tempImportedData
;       
select * from @tempEngine ;
select * from @tempImportedData ;

I've commented out two lines starting with OUTPUT.

The problem with the first is that it inserts all of the correct data into @tempImportedData, so the end result is that 16 rows exist, the first 8 are the same with a null value for engine_id while the third column is null; the remaining 8 have all three columns populated. The end result should have 8 rows not 16.

The second OUTPUT statement has the same problem as the first - 16 rows instead of 8. However the new 8 rows contain null, null, engine_id

So how can I alter this TSQL to get @tempImportedData.engine_id updated without inserting new rows?

Answer 1

You need another table variable (@temp) to capture the output from the insert and then run a update statement using the @temp against @tempImportedData joining on c1 and c2. This requires that the combination of c1 and c2 is unique in @tempImportedData.

Declare @temp as table(
     id int
    ,c1 int
    ,c2 int
);

INSERT INTO @tempEngine ( c1, c2 ) 
    OUTPUT INSERTED.id, INSERTED.c1, INSERTED.c2 INTO @temp
    SELECT 
         c1
        ,c2
    FROM 
        @tempImportedData
;       

UPDATE T1
  SET engine_id = T2.id
FROM @tempImportedData as T1
  INNER JOIN @temp as T2
    on T1.c1 = T2.c1 and
       T1.c2 = T2.c2
;