Get ID of inserted & Selected row after multiple insert

Question

I have a query running in loop which I am trying to optimize as this

INSERT INTO myTable (col1, col2, col3)
OUTPUT inserted.id, SOURCE_ROW_ID_NEEDED_HERE
    SELECT col1, col2, col3 
    FROM myTable 
    WHERE col2 = 20 --any value

My problem is : col2 = 20 can have N number of rows which get inserted, I need the id of the source row for the new record. For example say there are 3 rows for col2 = 20 and id of them are 11,12,15. The new inserted ID are say 150,151,152.

I would need

11  150
12  151
15  152

You can only put columns in `output` that are in the records being inserted into the new table. So if the `id` is being inserted, you can include it in the output. — Gordon Linoff, Nov 26 '18 at 18:37
@GordonLinoff, thanks. I reckon that would be the case. Any hack to achieve desired result? — CFML_Developer, Nov 26 '18 at 18:40
does the source table have an id column? If so, include it in the insert statement and output it via the output clause — user1443098, Nov 26 '18 at 18:47
You might be able to use this existing answer and go from there: https://stackoverflow.com/questions/35687950/combine-output-inserted-id-with-value-from-selected-row — TTeeple, Nov 26 '18 at 18:48
@user1443098, yes it has. But it is an Identity key. I thought this was clear with the sql I posted :) — CFML_Developer, Nov 26 '18 at 18:48
Clear enough! So insert that key in the target table, then you have it, — user1443098, Nov 26 '18 at 18:54
Source & target tables are same. I can not insert into identity column. — CFML_Developer, Nov 26 '18 at 18:57
OK -- so I'm missing the point of this. Why are you re-inserting rows in the table? (and, you could add a new column to the table to hold the source id number) — user1443098, Nov 26 '18 at 19:04

score 0 · Answer 1 · answered Nov 26 '18 at 19:09

Are you looking for something like

CREATE TABLE T1(
  Col1 INT IDENTITY(1, 1),
  Col2 INT,
  Col3 INT
);

CREATE TABLE T2(
  Col1 INT IDENTITY(1, 1),
  Col2 INT,
  Col3 INT
);

INSERT INTO T2(Col2, Col3) VALUES
(11,  150),
(12,  151),
(15,  152);

DECLARE @TT TABLE (ID INT, Col2 INT);

SET IDENTITY_INSERT T1 ON;

INSERT INTO T1 (Col1, Col2, Col3)
OUTPUT INSERTED.Col1,
       INSERTED.Col2
INTO @TT (ID, Col2)
SELECT Col1,
       Col2,
       Col3
FROM T2;

SELECT *
FROM @TT;

SET IDENTITY_INSERT T1 OFF;

Demo

Get ID of inserted & Selected row after multiple insert

1 Answers1