Insert Into... Merge... Select (SQL Server)

Question

I could use your expertise. I have the following code:

INSERT INTO Table3 (Column2, Column3, Column4, Column5)
SELECT null, 110, Table1.ID, Table2.Column2
FROM Table1
     JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
     JOIN Table2 on Table1Table2Link.Column2=Table2.ID

Now I need to take the Inserted.ID (Table3's Identity that is generated on insert) and Table2.ID and insert them into either a temporary table or a table variable. Normally I would use the OUTPUT clause, but OUTPUT cannot get data from across different tables. Now I believe it can be done with MERGE but I am not sure how to go about it. I need something like:

INSERT INTO Table3 (Column2, Column3, Column4, Column5)
OUTPUT Inserted.ID, Table2.ID into @MyTableVar
SELECT null, 110, Table1.ID, Table2.Column2
FROM Table1
     JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
     JOIN Table2 on Table1Table2Link.Column2=Table2.ID

I apologize if this is a duplicate question but I could not find anything.

You might need to do this by inserting the values you want to insert into a temporary table and then using `OUTPUT` to get the new output value. — Gordon Linoff, Dec 16 '16 at 12:24

score 8 · Accepted Answer · answered Dec 16 '16 at 12:30

8

The trick is to populate the table with the MERGE statement instead of an INSERT...SELECT. That allowes you to use values from both inserted and source data in the output clause:

MERGE INTO Table3 USING
(
    SELECT null as col2, 
           110 as col3, 
           Table1.ID as col4, 
           Table2.Column2 as col5,
           Table2.Id as col6
    FROM Table1
    JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
    JOIN Table2 on Table1Table2Link.Column2=Table2.ID
) AS s ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (Column2, Column3, Column4, Column5)
VALUES (s.col2, s.col3, s.col4, s.col5)
OUTPUT Inserted.ID, s.col6
INTO @MyTableVar (insertedId, Table2Id);

answered Dec 16 '16 at 12:30

Zohar Peled

79,642
10
69
121

Thank you for your help and the quick response! – Kyle Pierson Dec 16 '16 at 12:48
Never know this.. +1 – Pரதீப் Dec 28 '16 at 15:48
@Prdp: I found out about it myself only when I had to add multiple parent - children data. You might find [my solution to this](http://stackoverflow.com/questions/38213008/t-sql-insert-data-into-parent-and-child-tables) helpful someday. – Zohar Peled Dec 28 '16 at 16:40
1

The wonders of T-SQL. So glad other folks figured out this was a need! – str8ball Apr 22 '22 at 17:04

Insert Into... Merge... Select (SQL Server)

1 Answers1

Linked

Related