插入...合并...选择 (SQL Server) [英] Insert Into... Merge... Select (SQL Server)

问题描述

我可以利用你的专业知识.我有以下代码:

I could use your expertise. I have the following code:

INSERT INTO Table3 (Column2, Column3, Column4, Column5)
SELECT null, 110, Table1.ID, Table2.Column2
FROM Table1
     JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
     JOIN Table2 on Table1Table2Link.Column2=Table2.ID

现在我需要将 Inserted.ID(在插入时生成的 Table3 的标识)和 Table2.ID 插入到临时表或表变量中.通常我会使用 OUTPUT 子句，但 OUTPUT 无法从不同的表中获取数据.现在我相信它可以通过 MERGE 完成，但我不知道如何去做.我需要类似的东西:

Now I need to take the Inserted.ID (Table3's Identity that is generated on insert) and Table2.ID and insert them into either a temporary table or a table variable. Normally I would use the OUTPUT clause, but OUTPUT cannot get data from across different tables. Now I believe it can be done with MERGE but I am not sure how to go about it. I need something like:

INSERT INTO Table3 (Column2, Column3, Column4, Column5)
OUTPUT Inserted.ID, Table2.ID into @MyTableVar
SELECT null, 110, Table1.ID, Table2.Column2
FROM Table1
     JOIN Table1Table2Link on Table1.ID=Table1Table2Link.Column1
     JOIN Table2 on Table1Table2Link.Column2=Table2.ID

如果这是一个重复的问题，我深表歉意，但我找不到任何东西.

I apologize if this is a duplicate question but I could not find anything.

推荐答案

诀窍是使用 MERGE 语句而不是 INSERT...SELECT 来填充表.这允许您在输出子句中使用来自插入数据和源数据的值:

The trick is to populate the table with the MERGE statement instead of an INSERT...SELECT. That allowes you to use values from both inserted and source data in the output clause:

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

这篇关于插入...合并...选择 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！