SQL Server 2008中SQL增强之三：Merge(在一条语句中使用Insert,Update,Delete)

时间：2022-06-04 00:58:15

 SQL Server 2008中SQL应用系列--目录索引

 

SQL　Server 2008提供了一个增强的SQL命令Merge，用法参看MSDN:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx

功能：根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。

我们看一个例子，假如，有一总产品列表，一个分店产品列表，需要从分店添加产品时更新总产品列表。

总产品表，分店产品表结构完全一致：

if OBJECT_ID ( ' Demo_AllProducts ' ) is not null
drop table Demo_AllProducts
 go
Create table Demo_AllProducts
(PKID int not null identity ( 1 , 1 ) primary key
,DName Nvarchar ( 20 ) null
,DCode NVarchar ( 30 ) null
,DDate datetime null
)
 go

-- this SQL is only for SQL Server 2008
Insert into Demo_AllProducts
(DName,DCode,DDate)
 values
( ' DemoA ' , ' AAA ' , GETDATE ()),
( ' DemoB ' , ' BBB ' , GETDATE ()),
( ' DemoC ' , ' CCC ' , GETDATE ()),
( ' DemoD ' , ' DDD ' , GETDATE ()),
( ' DemoE ' , ' EEE ' , GETDATE ())

 select * from Demo_AllProducts

 -- PKID DName DCode DDate
-- 1 DemoA AAA 2010-10-12 20:33:54.417
-- 2 DemoB BBB 2010-10-12 20:33:54.417
-- 3 DemoC CCC 2010-10-12 20:33:54.417
-- 4 DemoD DDD 2010-10-12 20:33:54.417
-- 5 DemoE EEE 2010-10-12 20:33:54.417
if OBJECT_ID ( ' Demo_Shop1_Product ' ) is not null
drop table Demo_Shop1_Product
 go

Create table Demo_Shop1_Product
(PKID int not null identity ( 1 , 1 ) primary key
,DName Nvarchar ( 20 ) null
,DCode NVarchar ( 30 ) null
,DDate datetime null
)
 go

-- this SQL is only for SQL Server 2008
Insert into Demo_Shop1_Product
(DName,DCode,DDate)
 values
( ' DemoA ' , ' AAA ' , GETDATE ()),
( ' DemoB ' , ' CCC ' , GETDATE ()),
( ' DemoF ' , ' FFF ' , GETDATE ())

 select * from Demo_Shop1_Product

 -- PKID DName DCode DDate
-- 1 DemoA AAA 2010-10-17 20:19:32.767
-- 2 DemoB CCC 2010-10-17 20:19:32.767
-- 3 DemoF FFF 2010-10-17 20:19:32.767

假定现在需要将分店数据完全合并到总产品表中，以编码字段为依据，如果产品名称不致，则用分店的产品名称替换总产品名称。

如果总产品表中不存在，则添加。

可选项：如果分店表中不存在，则从总产品表中删除分店中没有的行。如果这样，总产品表和分店表就完全同步了。实际操作中可能不需要删除目标表的行。

语句如下：

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

此时，执行完成后，两个表的行均如下：

-- PKID DName DCode DDate
-- 1 DemoA AAA 2010-10-17 20:31:00.827
-- 2 DemoB CCC 2010-10-17 20:31:00.827
-- 3 DemoF FFF 2010-10-17 20:31:00.827

如果不删除，语句如下：

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

执行后结果：

-- PKID DName DCode DDate
-- 1 DemoA AAA 2010-10-17 20:30:28.350
-- 2 DemoB BBB 2010-10-17 20:30:28.350
-- 3 DemoB CCC 2010-10-17 20:30:28.350
-- 4 DemoD DDD 2010-10-17 20:30:28.350
-- 5 DemoE EEE 2010-10-17 20:30:28.350
-- 6 DemoF FFF 2010-10-17 20:31:00.827

-- PKID DName DCode DDate
-- 1 DemoA AAA 2010-10-17 20:31:00.827
-- 2 DemoB CCC 2010-10-17 20:31:00.827
-- 3 DemoF FFF 2010-10-17 20:31:00.827

如果需要记录Merge语句影响的行，可以用Output子句，如果仅仅需要知道影响的行数，可以使用@@ROWCOUNT或ROWCOUNT_BIG()，修改后的示例如下：

-- 定义表变量以存储输出
Declare @tableVarRecord Table
(MPKID int not null identity ( 1 , 1 ) primary key
,PKID int null
,DName Nvarchar ( 20 ) null
,DCode NVarchar ( 30 ) null
,DDate datetime null
)

 -- 确定目标表
Merge Into Demo_AllProducts p
 -- 从数据源查找编码相同的产品
using Demo_Shop1_Product s on p.DCode = s.DCode
-- 如果编码相同，则更新目标表的名称
When Matched and P.DName <> s.DName Then
Update set P.DName = s.DName

 -- 如果目标表中不存在，则从数据源插入目标表
When Not Matched By Target Then
Insert (DName,DCode,DDate) values (s.DName,s.DCode,s.DDate)

 -- 如果数据源的行在源表中不存在，则删除目标表行
When Not Matched By Source Then
Delete OUTPUT deleted. * INTO @tableVarRecord ;
 -- --Delete OUTPUT Inserted.* INTO @tableVarRecord;

-- 返回上个Merge语句影响的行数
select @@ROWCOUNT as Count1,ROWCOUNT_BIG() as Count2

select * from @tableVarRecord ;

结果：

-- 影响的行数
-- Count1 Count2
-- 5 5

-- Deleted表的行
-- MPKID PKID DName DCode DDate
-- 1 NULL NULL NULL NULL
-- 2 2 DemoB BBB 2010-10-17 21:42:30.700
-- 3 3 DemoC CCC 2010-10-17 21:42:30.700
-- 4 4 DemoD DDD 2010-10-17 21:42:30.700
-- 5 5 DemoE EEE 2010-10-17 21:42:30.700

关于@@ROWCOUNT和ROWCOUNT_BIG()的更多说明，请查阅MSDN:

http://technet.microsoft.com/zh-tw/library/ms187316.aspx 

http://msdn.microsoft.com/en-us/library/ms181406.aspx

如果影响的结果超过20亿，即整型的最大范围，请使用后者。
 

标签：

相关文章