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>
<pre class="brush:php;toolbar:false"> JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
 JOIN Table2 on Table1Table2Link.Column2=Table2.ID

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>
<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

) 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);

By using MERGE, the OUTPUT clause can obtain both the inserted identity (Inserted.ID) and the value from Table2 (Table2.ID) and insert them into @MyTableVar for further processing.

The above is the detailed content of How Can MERGE Statement Retrieve Generated Identity and Source Data in SQL Server INSERT Operations?. For more information, please follow other related articles on the PHP Chinese website!