sql - 您能否在 T-SQL MERGE 部分执行更多操作？

Question

我的尝试相当成功，我需要识别和巩固 OUTPUT 列(其中 #)以及 MERGE 语句的哪一部分与 #of 列真正相关。我只是问我能否像下面的示例那样在一个 MERGE 条件内执行多个操作？

WHEN MATCHED
AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate

THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 

--MAGIC HERE ??
--CAN I ALSO 
--INSERT  in this same section before I do more in below condition?

WHEN NOT MATCHED BY TARGET THEN 

我看过从 MERGE 语句的 OUTPUT 部分的内容中完成 INSERT 的示例，但是如果不使用来自 OUTPUT 结果的另一个查询，我可以完成我提议的操作吗？

Answer 1

您不能在一个子句中执行多项操作，但您可以从 OUTPUT 子句中获益以执行更多语句。

基于此Microsoft Documentation :

The OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

以上文章包含许多可以帮助您的示例。

引用资料

• MERGE (Transact-SQL) (检查 OUTPUT 子句部分)
• TSQL Merge - Multiple statements after THEN
• The OUTPUT Clause for the MERGE Statements