java sql merge_SQL2008中的MERGE使用心得 | 学步园

MSDN:

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

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

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次，而这是不允许的

--但是可以一次更新多行(原表中只有一条记录且对应目标表中的多条，那么目标表会同时进行操作，比如更新)

--不可多次更新一行(即原表中有多行对应目标表中的一行)，只可一次更新一条或多条。