MERGE 的语法为：
| 以下是引用片段： [ WITH <common_table_expression> [,...n] ] MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] target_table [ [ AS ] table_alias ] [ WITH ( <merge_hint> ) ] USING <table_source> ON <search_condition> [ WHEN MATCHED [ AND <search_condition> ] THEN <merge_matched> ] [ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ] THEN <merge_not_matched> ] [ WHEN SOURCE NOT MATCHED [ AND <search_condition> ] THEN <merge_ matched> ] <output_clause> [ OPTION ( <query_hint> [ ,...n ] ) ] ; <merge_hint>::= { [ <table_hint_limited> [ ,...n ] ] [ [ , ] INDEX ( index_val [ ,...n ] ) ] } <table_source> ::= { table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] [ WITH ( table_hint [ [ , ]...n ] ) ] | rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [ ,...n ] ) ] | user_defined_function [ [ AS ] table_alias ] | OPENXML <openxml_clause> | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | <joined_table> | <pivoted_table> | <unpivoted_table> } <merge_matched>::= <set_clause>::= | { udt_column_name. { { property_name = expression | field_name = expression } | method_name ( argument [ ,...n ] ) } } | column_name | @variable = expression } [ ,...n ] <merge_not_matched>::= INSERT [ ( <column_list> ) ] { VALUES ( <values_list> ) | DEFAULT VALUES } <output_clause>::= { [ OUTPUT <dml_select_list> INTO [ (column_list) ] ] [ OUTPUT <dml_select_list> ] } <dml_select_list>::= [ [AS] column_alias_identifier ] [ ,...n ] <column_name> ::= . | $ACTION |
示例：在一条 SQL 语句中使用 WHERE 在一张表上执行 UPDATE 和 DELETE 操作
| 以下是引用片段： USE AdventureWorks; GO MERGE Production.ProductInventory AS pi USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID AND soh.OrderDate = GETDATE() GROUP BY ProductID) AS src (ProductID, OrderQty) ON (pi.ProductID = src.ProductID) WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty WHEN MATCHED AND pi.Quantity - src.OrderQty = 0 THEN DELETE; |
这个示例是一个非常典型的销售定货库存问题。这个示例很简单，表达的意思就是：如果某一个产品产生了销售定单数据，则将其对应的产品库存除去该销售定单所产生的数量，如果当前库存数量与该销售定单数量相同，则从库存表中删除该产品的库存纪录。
我们看到，利用 MERGE 语句可以将复杂的 SQL 语句简化。它比起 IF、CASE 等更加灵活和强大。
结论
Microsoft SQL Server 2008 对事务性 SQL
