Merge关键字，在SQL2008中被使用，之前的版本均没有该关键字。
一、Merge关键字的概念及功能
Merge关键字是一个神奇的DML关键字。它在SQL Server 2008版本或更高版本中使用，它能将In sert,Up date,De lete语句，合并为一句。
MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。”,通过这个描述，我们可以看出Merge是关于对于两个表之间的数据进行操作的。
可以这样来理解：Merge语法是对插入，更新，删除这三个操作的合并。根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。
在如下的场合使用Merge关键字，将非常有用。
数据同步
数据转换
基于源表对目标表做In sert,Up date,De lete操作
我这里用一个简单的例子来解释一下
USE tempdb
GO
创建一个临时的订单表
CREATE TABLE Orders(OrderID INT,CustomerID NCHAR(5))
GO
往这个表中添加两行记录
IN SERT INTO Orders VA LUES(1,N'AAAAA')
IN SERT INTO Orders VA LUES(2,N'BBBBB')
GO
通过生成表查询，产生另外一个架构一模一样的表，但只是复制了第一行数据过去
SELECT * INTO Orders2 FROM Orders WHERE OrderID=1
GO
将第二个表的数据进行更新
UP DATE orders2 S ET CustomerID=N'DDDDD'
合并两个表
MERGE Orders o
USING Orders2 o2 ON o2.OrderID=o.OrderID
WHEN MATCHED THEN UP DATE S ET O.CustomerID=o2.CustomerID--如果匹配到了，就更新掉目标表
WHEN NOT MATCHED THEN IN SERT VA LUES(o2.OrderID,o2.CustomerID)--如果匹配不到，就插入
WHEN NOT MATCHED BY SOURCE THEN DELETE;--如果来源表无法匹配到，就删除
二、MERGE语法
[ WITH <common_table_expression> [,...n] ]
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_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 ] ) ]
| rows et_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_search_condition> ::=
<search_condition>
<merge_matched>::=
{ UP DATE S ET <s et_clause> | DELETE }
<s et_clause>::=
S ET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] ) }
}
| column_name { .WRITE ( expression , @Offs et , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
<merge_not_matched>::=
{
IN SERT [ ( column_list ) ]
{ VA LUES ( va lues_list )
| DEFAULT VA LUES }
}
<clause_search_condition> ::=
<search_condition>
<search condition> ::=
{ [ NOT ] <predicate> | ( <search_condition> ) }
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]
<predicate> ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression
[ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS
( { column | * } , '< contains_search_condition >' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }
<output_clause>::=
{
[ OUTPUT <dml_select_list> ]
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
[ (column_list) ] ]
}
<dml_select_list>::=
{ <column_name> | scalar_expression }
[ [AS] column_alias_identifier ] [ ,...n ]
<column_name> ::=
{ DELETED | IN SERTED | from_table_name } . { * | column_name }
| $action