SQLServer2008的实用小道具 merger使用介绍

a. 使用 merge 在单个语句中对表执行 update 和 delete 操作
　　下面的示例使用 merge 根据 salesorderdetail 表中已处理的订单，每天更新 adventureworks 示例数据库中的 productinventory 表。通过减去每天对 salesorderdetail 表中的每种产品所下的订单数，更新 productinventory 表的 quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少，则会从 productinventory 表中删除该产品对应的行。
　　b. 借助派生的源表，使用 merge 对目标表执行 update 和 insert 操作
　　下面的示例使用 merge 以更新或插入行的方式来修改 salesreason 表。当源表中的 newname 值与目标表 (salesreason) 的 name 列中的值匹配时，就会更新此目标表中的 reasontype 列。当 newname 的值不匹配时，就会将源行插入到目标表中。此源表是一个派生表，它使用 transact-sql 行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息，请参阅 from (transact-sql)。

　　c. 将 merge 语句的执行结果插入到另一个表中
　　下例捕获从 merge 语句的 output 子句返回的数据，并将该数据插入另一个表。merge 语句根据在 salesorderdetail 表中处理的订单，更新 productinventory 表的 quantity 列。本示例捕获已更新的行，并将这些行插入用于跟踪库存变化的另一个表中

use adventureworks;
go
if object_id (n'production.usp_updateinventory', n'p')
is not null drop procedure production.usp_updateinventory;
go
create procedure production.usp_updateinventory
@orderdate datetime
as
merge production.productinventory as target
using (select productid, sum(orderqty) from sales.salesorderdetail as sod
join sales.salesorderheader as soh
on sod.salesorderid = soh.salesorderid
and soh.orderdate = @orderdate
group by productid) as source (productid, orderqty)
on (target.productid = source.productid)
when matched and target.quantity - source.orderqty <= 0
then delete
when matched
then update set target.quantity = target.quantity - source.orderqty,
target.modifieddate = getdate()
output $action, inserted.productid, inserted.quantity, inserted.modifieddate, deleted.productid,
deleted.quantity, deleted.modifieddate;
go
execute production.usp_updateinventory '20030501'

use adventureworks;
go
merge into sales.salesreason as target
using (values ('recommendation','other'), ('review', 'marketing'), ('internet', 'promotion'))
as source (newname, newreasontype)
on target.name = source.newname
when matched then
update set reasontype = source.newreasontype
when not matched by target then
insert (name, reasontype) values (newname, newreasontype)
output $action, inserted.*, deleted.*;

use adventureworks;
go
merge into sales.salesreason as target
using (values ('recommendation','other'), ('review', 'marketing'), ('internet', 'promotion'))
as source (newname, newreasontype)
on target.name = source.newname
when matched then
update set reasontype = source.newreasontype
when not matched by target then
insert (name, reasontype) values (newname, newreasontype)
output $action, inserted.*, deleted.*;

use adventureworks;
go
create table production.updatedinventory
(productid int not null, locationid int, newqty int, previousqty int,
constraint pk_inventory primary key clustered (productid, locationid));
go
insert into production.updatedinventory
select productid, locationid, newqty, previousqty
from
( merge production.productinventory as pi
using (select productid, sum(orderqty)
from sales.salesorderdetail as sod
join sales.salesorderheader as soh
on sod.salesorderid = soh.salesorderid
and soh.orderdate between '20030701' and '20030731'
group by productid) as src (productid, orderqty)
on pi.productid = src.productid
when matched and pi.quantity - src.orderqty >= 0
then update set pi.quantity = pi.quantity - src.orderqty
when matched and pi.quantity - src.orderqty <= 0
then delete
output $action, inserted.productid, inserted.locationid, inserted.quantity as newqty, deleted.quantity as previousqty)
as changes (action, productid, locationid, newqty, previousqty) where action = 'update';
go