SQLServer 2008 Merge 的机制探讨

SQL Server 2008 引入了一个Merge关键字，MSDN有对这个关键字的解释和范例。传送门：http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 微软官方解释是： 根据与源表联接的结果，对目标表执行插入、更新或删除操作。 例如，根据在另一个表中找到的差异

SQL Server 2008 引入了一个Merge关键字，MSDN有对这个关键字的解释和范例。传送门：http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 微软官方解释是：根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。如果这个官方解释用白话文说就是：插入，更新和删除一步到位！

场景1：数据监测

这里有一个场景是，有一个旧总的商品表（AllProduct），还有一个新采集的商品表（Product）。总的商品表里面的数据比较陈旧，一些新的数据还没有加到里面去，而且有些旧的数据不正确。我们现在要根据商品Code来比对，如果旧表与新表的Code相同，但是名字不同，以新表的名字为准，更新旧表里面的商品名称。如果新的商品的记录在旧表里面不存在，那么把新表里面的数据插入到旧表里面去。新表和旧表的表结构完全相同。说了这么多，可能有点绕，那么总结下就是：

1）两张表（新表和旧表）的结构相同数据不同；

2）新表里面的数据如果不存在（根据主键进行匹配）于旧表，则把新表的数据插入旧表

3）旧表中的记录的ID如果和新表的记录的ID相同，但是Name不同，则将更新旧表中的Name为新表中的Name

初始化环境的Sql代码如下：

if OBJECT_ID('AllProducts') is not null  
drop table AllProducts  
go  

Create table AllProducts
(
	P_ID int not null identity(1,1) primary key,
	P_Name Nvarchar(20) null,
	P_Code NVarchar(30) null,
	P_Date datetime null
)
go 

Insert into AllProducts (P_Name,P_Code,P_Date) values 
('ProductA','Pro_A',GETDATE()),
('ProductB','Pro_B',GETDATE()),  
('ProductB','Pro_C',GETDATE()),  
('ProductD','Pro_D',GETDATE()),
('ProductE','Pro_E',GETDATE())  

if OBJECT_ID('Product') is not null  
drop table Product  
go 
Create table Product 
(
	P_ID int not null identity(1,1) primary key,
	P_Name Nvarchar(20) null,
	P_Code NVarchar(30) null,
	P_Date datetime null
)
go  

Insert into Product (P_Name,P_Code,P_Date)  
values  
('ProductA','Pro_A',GETDATE()),
('ProductC','Pro_C',GETDATE()),
('ProductF','Pro_F',GETDATE())

select * from AllProducts;
select * from Product;

输出结果如下：

根据我们确定的规则，即将发生如下更新和插入：

1）更新：AllProduct表中的name为Pro_A和name为Pro_C的记录会被匹配到，然后由于Pro_C对应的Name不同，所以AllProecudt表中的Name会被更新。

2）插入：AllProduct表中没有Pro_F这条记录，所以会被插入到AllProduct表中

我们使用Merge的sql如下：

--确定目标表
Merge Into AllProducts p
--从数据源查找编码相同的产品
using Product s on p.P_Code=s.P_Code
--如果编码相同，则更新目标表的名称
When Matched and P.P_Names.P_Name Then Update set P.P_Name=s.P_Name
--如果目标表中不存在，则从数据源插入目标表
--When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate);
When Not Matched By Target Then Insert values (s.P_Name,s.P_Code,s.P_Date);

上面注释的那条语句和下面的那个意义相同，只是没有那么简洁。执行结果如下：

第一条记录已经被更新，第二条记录是从Product表中插入的，是不是真的很简洁。

场景2：数据表同步

如果希望AllProdct和Product表一模一样，即数据同步，那么我们需要删掉Product表中不存在于AllProduct表中的记录。整个sql如下：

--确定目标表
Merge Into AllProducts p
--从数据源查找编码相同的产品
using Product s on p.P_Code=s.P_Code
--如果编码相同，则更新目标表的名称
When Matched and P.P_Names.P_Name Then Update set P.P_Name=s.P_Name
--如果目标表中不存在，则从数据源插入目标表
--When Not Matched By Target Then Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate);
When Not Matched By Target Then Insert values (s.P_Name,s.P_Code,s.P_Date)
--如果数据源的行在目标表中不存在，则删除源表行
When Not Matched By Source Then Delete;

总结：Merge是同时做插入和更新，效率据说更高。