SQl Server 2008之Merge示例

在SQL Server 2008的bookonline中对Merge操作的解释为:

此操作用处比较广泛，能很好的简化数据更新的过程。其有三个子句：主键匹配更新操作，目标不存在插入操作，源不存在删除操作。当然三个操作在同一个过程中并非必须全部指定，但至少要指定一个操作。具体的语法比较苦涩。下面以具体的例子进行说明：

有两张结构一致的表：test1,test2。建表语句如下：

1.将test1同步到test2中，没有的数据进行插入，已有数据进行更新

运行以下查询查看更新结果

select a.id,a.name as name_1,b.name as name_2  from test1 as a,test2 as b 
where a.id=b.id

id          name_1               name_2 
----------- -------------------- -------------------- 
1           boyi55               boyi55 
2           51cto                51cto 
3           bbs                  bbs 
4           fengjicai            fengjicai 
5           alis                 alis

2.test1中ID为5的记录删除后，用merge同步更新test2。并显示更新内容。

delete from test1 where id=5

go

merge   test2 t 
using test1 s 
on t.id=s.id 
when matched 
then update set t.name=s.name 
when not matched then insert values(id,name);--此处by target可以省略 
when not matched by source then delete--目标中存在源中没有的记录时删除。此处by source不能省略 
output $action ,inserted.name  as new_name,deleted.name as old_name;--输出更新前后name的变化

$action    new_name             old_name 
---------- -------------------- -------------------- 
UPDATE     boyi55               boyi55 
UPDATE     51cto                51cto 
UPDATE     bbs                  bbs 
UPDATE     fengjicai            fengjicai 
DELETE     NULL                 alis

3.我们看到上面存在一个问题就是相同的记录也变更新了。因此可以在matched子句中添加一个条件，当更新的列数值不同时再进行更新。在下面的例子中将新建一个表把更新记录写入到日志表中，并且只更新不同记录。

create table test3(action varchar(20),new_name varchar(20),old_name varchar(20))

update test1 
set name='boyi555' 
where id=1

go 
merge   test2 t 
using test1 s 
on t.id=s.id 
when matched and t.name!=s.name 
then update set t.name=s.name 
when not matched then insert values(id,name) 
when not matched by source then delete 
output $action ,inserted.name,deleted.name into test3(action,new_name,old_name);

select* from test3

action               new_name             old_name 
-------------------- -------------------- -------------------- 
UPDATE               boyi555              boyi55

4.我们只将删除操作记录到日志中。

update test1 
set name='bbs.51cto.com' 
where id=1 
go 
delete from test1 where id=3 
go 
insert into test3 
select action,new_name,old_name from 
( 
merge   test2 t 
using test1 s 
on t.id=s.id 
when matched and t.name!=s.name 
then update set t.name=s.name 
when not matched then insert values(id,name) 
when not matched by source then delete 
output $action ,inserted.name ,deleted.name) as del(action,new_name,old_name) 
where action='delete'

action               new_name             old_name 
-------------------- -------------------- -------------------- 
DELETE               NULL                 bbs

5.如果数据量比较大，同步操作分两次完成，每次只更新50%的数据。

update test1 
set name=name+'boyi55' 
go

merge top (50) percent  test2 t 
using test1 s 
on t.id=s.id 
when matched and t.name!=s.name 
then update set t.name=s.name 
when not matched then insert values(id,name) 
when not matched by source then delete 
output $action ,inserted.name ,deleted.name;

(3 row(s) affected) 
$action    name                 name 
---------- -------------------- -------------------- 
UPDATE     bbs.51cto.comboyi55  bbs.51cto.com 
UPDATE     51ctoboyi55          51cto

(2 row(s) affected)