SQL2008中的MERGE使用心得

MSDN:

一、MERGE 语句最多可以有两个 WHEN MATCHED 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <search_condition> 子句。对于任何给定的行，只有在未应用第一个 WHEN MATCHED 子句的情况下，才会应用第二个 WHEN MATCHED 子句。如果有两个 WHEN MATCHED 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。如果在 <merge_matched> 子句中指定了 UPDATE，并且根据 <merge_search_condition>，<table_source> 中的多个行与 target_table 中的某一行匹配，则 SQL Server 将返回错误。MERGE 语句无法多次更新同一行，也无法更新和删除同一行。

二、自己的理解：根据<merge_search_condition>,在<source_table>中对应有多条的记录，而在<target_table>中只有一条，那么将会报错：

      MERGE 语句试图多次更新或删除同一行。目标行与多个源行匹配时会出现这种情况。MERGE 语句无法多次更新/删除目标表的同一行。请简化 ON 子句，以确保目标行最多与一个源行匹配，也可以使用 GROUP BY 子句对源行分组。

      所以除了上面的那种情况下，其他情况都是正确的。

      下面是SQL语句：

use City;goif object_id(N'SourceTable',N'U') is not null drop table dbo.SourceTablego--创建原始表create Table SourceTable( ID int primary key identity(1,1) not null, ProvinceName nvarchar(20) not null, CityName nvarchar(20) not null, CityPopulation int not null)--go--insert into SourceTable values('北京','福建某市1',200)--insert into SourceTable values('湖南','湖南某市1',200)--insert into SourceTable values('湖南','湖南某市2',100)--insert into SourceTable values('湖南','湖南某市3',400)--insert into SourceTable values('浙江','浙江某市1',6600)--insert into SourceTable values('浙江','浙江某市2',500)--创建目标表if object_id(N'TargetTable',N'U') is not null drop table dbo.TargetTablegocreate Table TargetTable( ID int primary key identity(1,1) not null, ProvinceName nvarchar(20) not null, TotalPopulation int not null)--go--insert into TargetTable values('上海',20)go--创建操作日志表if object_id(N'NoteTable',N'U') is not null drop table dbo.NoteTablegocreate table NoteTable( ID int primary key identity(1,1) not null, OpType nvarchar(20) not null, DelProvinceName nvarchar(20) null, DelTotalPopulation nvarchar(20) null, AddProvinceName nvarchar(20) null, AddTotalPopulation nvarchar(20) null, OpTime datetime not null)go--创建存储过程if object_id(N'FirstPro',N'P') is not null drop procedure dbo.FirstProgocreate procedure FirstPro@ProvinceName nvarchar(20)as--将被操作的行对象中的数据存到操作记录表中insert into NoteTableselect OpType,DeleteP,DeleteT,InsertP,InsertT,TheTime from( Merge TargetTable as N using (select * from SourceTable where ProvinceName=@ProvinceName) as S on N.ProvinceName=S.ProvinceName when Matched and N.TotalPopulation>=200 then DELETE--删除的标准格式 when Matched then Update Set N.TotalPopulation=S.CityPopulation+N.TotalPopulation--更新的标准格式 when not Matched by Target then Insert (ProvinceName,TotalPopulation)values(S.ProvinceName,S.CityPopulation)--添加的标准格式 --when not Matched by Source then--这个加上的话，那么目标表就只能有一条记录，还真不知道为什么 -- DELETE --output存储的是上面每个DML操作的行对象，可以放回这些被操作的行对象 output $action as OpType,inserted.ProvinceName,inserted.TotalPopulation, deleted.ProvinceName,deleted.TotalPopulation, GETDATE()) as Changeds(OpType,InsertP,InsertT,DeleteP,DeleteT,TheTime);--给字段重命名go--提交创建存储过程--申明变量declare @ProvinceName nvarchar(20)declare @CityName nvarchar(20)declare @CityPopulation intset @ProvinceName='北京'set @CityName='南平'set @CityPopulation=1--给原始表添加一行update SourceTable Set CityPopulation=@CityPopulation where ProvinceName=@ProvinceNameif @@ROWCOUNT=0insert into SourceTable(ProvinceName,CityName,CityPopulation)values(@ProvinceName,@CityName,@CityPopulation)--执行存储过程exec dbo.FirstPro @ProvinceName=@ProvinceName--查询select * from NoteTableselect * from dbo.SourceTableselect * from dbo.TargetTable--MERGE 语句试图多次更新或删除同一行。目标行与多个源行匹配时会出现这种情况。MERGE 语句无法多次更新/删除目标表的同一行。--请简化 ON 子句，以确保目标行最多与一个源行匹配，也可以使用 GROUP BY 子句对源行分组。--原因，对于when matched then 下如果是Delete或者Update操作时，那么源数据库中不能有两条或者俩条以上数据--对应目标表(根据在using join操作的条件而言的)，即目标表和原始表只能有一条数据相对应，--如果原表有2条的话，那么在进行更新的时候是根据原表来，即目标表的那条记录要更新或者删除2次，而这是不允许的--但是可以一次更新多行(原表中只有一条记录且对应目标表中的多条，那么目标表会同时进行操作，比如更新)--不可多次更新一行(即原表中有多行对应目标表中的一行)，只可一次更新一条或多条。