当前位置:Gxlcms > mysql > SQLServer2008Merge的机制探讨

SQLServer2008Merge的机制探讨

时间:2021-07-01 10:21:17 帮助过:40人阅读

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_Name<>s.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_Name<>s.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是同时做插入和更新,效率据说更高。



人气教程排行