How Can MERGE Statement Retrieve Generated Identity and Source Data in SQL Server INSERT Operations?

Insert into... Merge... Select: Utilizing MERGE to Retrieve Generated Identity

In SQL Server, the INSERT...SELECT statement allows data insertion into a target table from another table or query. However, it has limitations when extracting values from multiple sources. A solution exists by employing the MERGE statement, which enables the retrieval of both inserted and source data.

To illustrate, consider the following situation:

INSERT INTO Table3 (Column2, Column3, Column4, Column5)<br>SELECT null, 110, Table1.ID, Table2.Column2<br>FROM Table1</p>
<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false"> JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
 JOIN Table2 on Table1Table2Link.Column2=Table2.ID

Copy after login

The need arises to capture the generated identity (Inserted.ID) from Table3 and Table2.ID and insert them into a temporary table or variable. While the OUTPUT clause is commonly used for this purpose, it cannot extract data from different tables.

The solution lies in using MERGE to populate the table instead of INSERT...SELECT. This allows access to both inserted and source values in the output clause:

MERGE INTO Table3 USING<br>(</p>
<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">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

Copy after login