How Can SQL Server's MERGE Statement Insert Data and Output Multiple Columns from Different Tables?

Inserting and Outputting Data Using MERGE in SQL Server

In this query, we seek to insert data into Table3 using a SELECT statement and capture both the inserted ID and another column value from another table. The initial attempt using the OUTPUT clause failed due to cross-table references.

To overcome this limitation, we leverage the MERGE statement. By populating Table3 using a MERGE statement, we can utilize values from both the inserted data and the source data in the output clause.

Here's the revised query:

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

• The MERGE statement populates Table3 with data from the subquery.
• The OUTPUT clause specifies that both the inserted ID and Table2.ID should be output to the temporary table @MyTableVar.
• The WHEN NOT MATCHED clause ensures that the data is inserted if it does not already exist in Table3.

By using MERGE in this manner, we can insert data and capture additional information from across multiple tables, including the inserted ID, in a single statement.

The above is the detailed content of How Can SQL Server's MERGE Statement Insert Data and Output Multiple Columns from Different Tables?. For more information, please follow other related articles on the PHP Chinese website!