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; go if object_id(N'SourceTable',N'U') is not null drop table dbo.SourceTable go --创建原始表 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.TargetTable go create 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.NoteTable go create 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.FirstPro go create procedure FirstPro @ProvinceName nvarchar(20) as --将被操作的行对象中的数据存到操作记录表中 insert into NoteTable select 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 int set @ProvinceName='北京' set @CityName='南平' set @CityPopulation=1 --给原始表添加一行 update SourceTable Set CityPopulation=@CityPopulation where ProvinceName=@ProvinceName if @@ROWCOUNT=0 insert into SourceTable(ProvinceName,CityName,CityPopulation)values (@ProvinceName,@CityName,@CityPopulation) --执行存储过程 exec dbo.FirstPro @ProvinceName=@ProvinceName --查询 select * from NoteTable select * from dbo.SourceTable select * from dbo.TargetTable --MERGE 语句试图多次更新或删除同一行。目标行与多个源行匹配时会出现这种情况。MERGE 语句无法多次更新/删除目标表的同一行。 --请简化 ON 子句，以确保目标行最多与一个源行匹配，也可以使用 GROUP BY 子句对源行分组。 --原因，对于when matched then 下如果是Delete或者Update操作时，那么源数据库中不能有两条或者俩条以上数据 --对应目标表(根据在using join操作的条件而言的)，即目标表和原始表只能有一条数据相对应， --如果原表有2条的话，那么在进行更新的时候是根据原表来，即目标表的那条记录要更新或者删除2次，而这是不允许的 --但是可以一次更新多行(原表中只有一条记录且对应目标表中的多条，那么目标表会同时进行操作，比如更新) --不可多次更新一行(即原表中有多行对应目标表中的一行)，只可一次更新一条或多条。