使用merge同时执行insert和update操作

    我们经常会有这样的需求，根据某个字段或多个字段查找表中的一行或多行数据，如果查找成功得到匹配项，更新其中的其他一个或多个字段；如果查找失败则将“某个字段或多个字段”作为新的一行中的数据插入到表中。第一种方法是先更新，然后根据@@rowcount判断是否有匹配项，如果没有则插入。先使用下面的 代码创建一个存储过程。

use AdventureWorks
go
create procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
as
begin
set nocount on;
update Production.UnitMeasure set Name=@Name where UnitMeasureCode=@UnitMeasureCode
if(@@ROWCOUNT=0)
  begin
  insert into Production.UnitMeasure(Name,UnitMeasureCode)values(@Name,@UnitMeasureCode)
  end
end
go

    记得见过这样的笔试题目，要求是插入不存在的行，只要把上面语句中的update改成select就可以了，当时没有写出来，现在恍然大悟，也许是在考察@@ROWCOUNT的用法吧。这个语句也可以使用merge语句实现。下面我们使用merge关键字来修改这个存储过程。

alter procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
as
begin
set nocount on
merge Production.UnitMeasure as target
using (select @UnitMeasureCode,@Name) as source (UnitMeasureCode,Name)
on (target.UnitMeasureCode=source.UnitMeasureCode)
when matched then update set Name=source.Name
when not matched then insert(UnitMeasureCode,Name)values(source.UnitMeasureCode,Name)
output deleted.*,$action,inserted.* into MyTempTable;
end
go
    这个语句使用merge修改存储过程，这个语句中又出现我不太了解的关键字using和$action。Using是用来指定和表InsertUnitMeasure中相匹配的数据源，这里的数据源来自外部输入，是通过两个输入参数得到。$action可能是一个占位符，表示上面的when字句进行的操作。至于inserted.*和deleted.* 就是插入和删除的数据行了，这个我在其中一篇文章中也提到，他们有点类似类中的this关键字，注意为了记录修改的过程我们需要创建一个临时表#MyTempTable来跟踪修改过程，所以在调用这个存储过程之前我们需要新建这个表，语句如下：

create table MyTempTable(
ExistingCode  nchar(3),
ExistingName  nvarchar(50),
ExistingDate  datetime,
ActionTaken   nvarchar(50),
NewCode       nchar(3),
[NewName]     nvarchar(50),      
NewDate       datetime
)
Go
    现在我们来执行下面的语句看看有什么样的结果:

1 exec InsertUnitMeasure @UnitMeasureCode = ‘ABC‘,@Name=‘New Test Value1‘
2 EXEC InsertUnitMeasure @UnitMeasureCode = ‘XYZ‘, @Name = ‘Test Value‘;
3 EXEC InsertUnitMeasure @UnitMeasureCode = ‘ABC‘, @Name = ‘Another Test Valuea‘;
4 Go

    首先使用语句：select * from Production.UnitMeasure order by ModifiedDate desc 来查看目标表中的数据变化如图1：

    这里虽然三次执行了存储过程，但是由于第一次和第三次的@UnitMeasureCode的值是相同的’ABC’所以第二次肯定是进行更新操作。所以最后表中新增了两条记录。然后使用下面的语句查看记录表MyTempTable中的跟踪信息如图2

    我们可以看到前面两条语句执行的是插入操作，所以原有的值都是空，因为在插入之前他们还不存在。第三条新型的是更新操作，更新UnitMeasureCode为’ABC’的记录。

作者：Tyler Ning
出处：http://www.cnblogs.com/tylerdonet/