SQl Server 2008之Merge示例

Mei笑D小妞

　　在SQL Server 2008的bookonline中对Merge操作的解释为:
根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。　　此操作用处比较广泛，能很好的简化数据更新的过程。其有三个子句：主键匹配更新操作，目标不存在插入操作，源不存在删除操作。当然三个操作在同一个过程中并非必须全部指定，但至少要指定一个操作。具体的语法比较苦涩。下面以具体的例子进行说明：
　　有两张结构一致的表：test1,test2。建表语句如下：
　　create table test1
　　(id int,name varchar(20))
　　go
　　create table test2
　　(id int,name varchar(20))
　　go
　　insert into test1(id,name)
　　values(1,'boyi55'),(2,'51cto'),(3,'bbs'),(4,'fengjicai'),(5,'alis')
　　insert into test2(id,name)
　　values(1,'boyi'),(2,'51cto')
　　1.将test1同步到test2中，没有的数据进行插入，已有数据进行更新
　　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);--目标主未知主键，插入。此语句必须以分号结束
　　运行以下查询查看更新结果
　　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>　　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>　　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>　　go

　　delete from test1 where>　　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)