How Can I Capture Data from Multiple Tables During an SQL Server INSERT Operation Using MERGE?

Insert Into... Merge... Select in SQL Server

In SQL Server, the INSERT INTO...SELECT statement allows you to insert data from a SELECT query into a target table. However, when the SELECT query involves data from multiple tables, the OUTPUT clause cannot capture data across different tables.

Introducing MERGE

To solve this issue, consider using the MERGE statement. MERGE combines INSERT, UPDATE, and DELETE operations into a single statement. It allows you to conditionally perform actions on target rows based on specific criteria.

Using MERGE for Output Data

To capture data from both inserted and source tables, you can use the OUTPUT clause within a MERGE statement. The following example demonstrates how to insert data from multiple tables and output the Inserted.ID and Table2.ID:

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

In this MERGE statement:

• The USING clause defines the source data from the SELECT query.
• The ON clause specifies that the MERGE operation will always be a NOT MATCHED condition (i.e., never update or delete).
• The WHEN NOT MATCHED clause defines the INSERT operation that inserts the data from the source table into Table3.
• The OUTPUT clause captures the Inserted.ID from the target table (Table3) and Table2.ID from the source table (s.col6) and stores them in the table variable @MyTableVar.

The above is the detailed content of How Can I Capture Data from Multiple Tables During an SQL Server INSERT Operation Using MERGE?. For more information, please follow other related articles on the PHP Chinese website!