欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-09-06 19:58:40
a. 使用 merge 在单个语句中对表执行 update 和 delete 操作   下面的示例使用 merge 根据 salesorderdetail 表中已处理的订单...
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