How Can I Efficiently Insert Data from Multiple Tables into a Temporary Table or Table Variable in SQL Server?

Inserting into Temporary Table or Table Variable Using MERGE

In SQL Server, OUTPUT clause cannot retrieve data from multiple tables, making it challenging to capture specific values during insert operations. However, MERGE statement provides a solution to this issue.

To insert data into a temporary table or table variable using MERGE:

1. Populate the temporary table or table variable with the MERGE statement. This allows you to retrieve values from both the inserted rows and the source data.
2. Use the OUTPUT clause in the MERGE statement to specify which columns to output.

Here's an 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 example, the MERGE statement populates the temporary table @MyTableVar with the Inserted.ID and Table2.ID values. The OUTPUT clause specifies that these two columns should be output into the temporary table.

This method allows you to efficiently capture and work with data from different tables during insert operations using the power of MERGE statements in SQL Server.

The above is the detailed content of How Can I Efficiently Insert Data from Multiple Tables into a Temporary Table or Table Variable in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!