【杂记--2008新特性Merge用法】

/*---------------

*问题描述：如何用一句SQL语句对表同时进行delete ，update 等操作

*解决方案：Merge语句(SQL 2008新语法)

------------------*/

---------------Merge简介----------------

/*

Merge语句的作用是根据与源表联接的结果，对目标表执行插入、更新或删除操作.

--主要作用是将另外一个表的数据合并到另外一个表中，被合并的表称为目标表，用来合并的表称为源表。

--这里的合并是广义的合并，包括插入，更新和删除操作。

*/

--格式(一般)：

 MERGE tbname --目标表

 using ()--这个括号里可以是表，视图，行集函数，连接表，派生表等多种形式

 on XXXX --指定目标表与源表的连接条件

 when matched --如果ON条件匹配，更新

 then update set .....

 when matched --如果ON条件匹配，删除

 then delete

 when not matched then.....;--分号结束

 

--PS：)当匹配时，用<merge_matched>指定进行的动作，可以指定更新或删除目标表中的数据。

--   2)当不匹配时，可以指定不匹配的动作。不匹配包括两种情况：

--     一是源表与目标表不同(默认的)：允许的动作只能是插入;

--     二是目标表与源表不匹配的情况:目标表与源表不匹配，则可以选择更新或删除。

------------------------------------------------------------------------------------------------

讲了这么多规则，看个例子

例子：

create table T1(id int,value int)

create table T2(id int,value int)

insert T1 values(1,1)

insert T1 values(2,2)

insert T1 values(3,3)

insert T1 values(4,4)

insert T2 values(1,1)

insert T2 values(2,4)

go

Merge T2 AS TARGE 

using T1 as sourc

on targe.id=sourc.id

when matched then update set targe.value=sourc.value

when not matched then insert values(sourc.id,sourc.value)

output $action,inserted.id,inserted.value,deleted.value ;--1.这里可以使用Output；.分号别忘记

--测试

select * from T2  

--drop table T1,T2

--这样就实现了2个表的同步

例子:(非原创)

/*以下代码使用MERGE 语句将源表dbo.Departments_delta 与目标表dbo.Departments 进行比较。

*此比较的搜索条件在该语句的ON 子句中定义。根据比较的结果，将执行以下操作。

*在表Departments 中，在源表和目标表中都存在的部门都将使用新名称、新经理或这两者进行更新。如果没有变化，则不进行任何更新。这是通过WHEN MATCHED THEN 子句完成的。

*在Departments 中不存在但存在于Departments_delta 中的所有部门，将插入到Departments 中。这是通过WHEN NOT MATCHED THEN 子句完成的。

*在Departments_delta 中不存在但存在于Departments 中的所有部门将从Departments 中删除。这是通过WHEN NOT MATCHED BY SOURCE THEN 子句完成的。

*/

MERGE dbo.Departments AS d 

USING dbo.Departments_delta AS dd 

    ON (d.DeptID = dd.DeptID) 

 WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName  THEN --注意这里可以使用附加条件（d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName ）

   UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName

 WHEN NOT MATCHED THEN --此处只能进行插入

   INSERT (DeptID, DeptName, Manager)VALUES (dd.DeptID, dd.DeptName, dd.Manager)

 WHEN NOT MATCHED BY SOURCE THEN ---注意这里当是目标表与源表不匹配的情况要用BY SOURCE 指出

   DELETE