How Can I Retrieve Output Values from Multiple Tables During an INSERT Operation?

Insert Into... Merge... Select: Harnessing Output Values from Multiple Tables

Suppose you need to insert data into a target table and additionally retrieve specific information from other tables involved in the insert operation. While the OUTPUT clause conveniently captures data from the inserted table, it falls short when sourcing values from multiple tables. In such scenarios, the MERGE statement emerges as a potent solution.

To achieve this, deviate from a simple INSERT...SELECT approach and employ a MERGE statement instead. This change allows you to leverage values from both the inserted and source data within the OUTPUT clause.

Here's an illustrative example:

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, a temporary table or table variable (@MyTableVar) is used to capture both Inserted.ID (the identity column of Table3) and Table2.ID. The non-existing equality condition in the ON clause (1 = 0) ensures that all incoming rows are always considered for insertion and data retrieval.

Thus, the MERGE statement effectively inserts data into Table3 and simultaneously populates @MyTableVar with the desired values from both the inserted and source tables. This allows you to work with the inserted data in conjunction with related information from other tables, expanding the utility of insert operations significantly.

The above is the detailed content of How Can I Retrieve Output Values from Multiple Tables During an INSERT Operation?. For more information, please follow other related articles on the PHP Chinese website!