--SQL Server2008 Merge应用实例 /******************************************************************************** *主题：SQL Server2008 Merge应用实例 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.12.5 *Mail:[email protected] *另外：转载请著名出处。 **********************************************************************************/
先不解释 看实例吧
有两个表如下
if OBJECT_ID('Stock') is not nulldrop table Stock gocreate table Stock (Symbol varchar(10) primary key,Qty int check(Qty>0))if OBJECT_ID('Trade') is not nulldrop table Trade gocreate table Trade (Symbol varchar(10) primary key,Delta int check(Delta<>0))--两个表，第一个表Stock为库存表 ，Trade为交易记录表--库存表的记录为：
insert into Stock values('ADVW',10)insert into Stock values('BYA',5)
--增加三条交易记录，
insert into Trade values('ADVW',5)insert into Trade values('BYA',-5)insert into Trade values('NWT',3)
查询两表的内容
select * from Stock
select * from Trade
Symbol Qty---------- -----------ADVW 10BYA 5(2 行受影响)Symbol Delta---------- -----------ADVW 5BYA -5NWT 3(3 行受影响)
在交易的时候就应该会影响到库存，这时候就用了merge 。等下我们还要用传统方法来处理然后对比下
merge Stock using Tradeon Stock.Symbol=Trade.Symbolwhen matched and (Stock.Qty+Trade.Delta=0) then --就是如果交易和库存一样的话就删除这条记录DELETEwhen matched then UPDATE SET Stock.Qty+=Trade.Delta --有交易也在库存里有 就减去交易when not matched then insert values(Trade.Symbol,Trade.Delta); --有交易没有库存的就加到库存里select * from Stockselect * from TradeSymbol Qty---------- -----------ADVW 15NWT 3(2 行受影响)Symbol Delta---------- -----------ADVW 5BYA -5NWT 3(3 行受影响)
使用Merge进行表复制
生成两个表，在存储过程执行一次复制一次
create table Original(PK int primary key ,Fname varchar(10),Number int)create table Replica(PK int primary key ,Fname varchar(10),Number int) create procedure uspSyncReplica as Merge Replica r using Original o on o.PK=r.PK WHEN MATCHED AND (o.Fname!=r.Fname or o.Number!=r.Number) then update set r.Fname=o.Fname , r.Number=o.Number when not matched then insert values(o.PK,o.Fname,o.Number) when not matched by source then delete; INSERT INTO Original VALUES(1,'Stephen',10) INSERT INTO Original VALUES(2,'Jack',20) INSERT INTO Original VALUES(3,'BEILL',30) select *from Original select *from Replica /* PK Fname Number----------- ---------- -----------1 Stephen 102 Jack 203 BEILL 30(3 行受影响)PK Fname Number----------- ---------- -----------(0 行受影响) */EXECUTE uspSyncReplica /* (3 行受影响) */ select *from Original select *from Replica /* PK Fname Number----------- ---------- -----------1 Stephen 102 Jack 203 BEILL 30(3 行受影响)PK Fname Number----------- ---------- -----------1 Stephen 102 Jack 203 BEILL 30(3 行受影响) */
在上面的存储过程执行一次复制一次 使用$action虚拟列查询MERGE输出如下
alter procedure uspSyncReplica as Merge Replica r using Original o on o.PK=r.PK WHEN MATCHED AND (o.Fname!=r.Fname or o.Number!=r.Number) then update set r.Fname=o.Fname , r.Number=o.Number when not matched then insert values(o.PK,o.Fname,o.Number) when not matched by source then delete output $action ,inserted.*,deleted.*; INSERT INTO Original VALUES(1,'Stephen',10) INSERT INTO Original VALUES(2,'Jack',20) INSERT INTO Original VALUES(3,'BEILL',30) select *from Original select *from Replica
EXECUTE uspSyncReplica$action PK Fname Number PK Fname Number---------- ----------- ---------- ----------- ----------- ---------- -----------INSERT 1 Stephen 10 NULL NULL NULLINSERT 2 Jack 20 NULL NULL NULLINSERT 3 BEILL 30 NULL NULL NULL
(3 行受影响)
查询复制后的表
PK Fname Number----------- ---------- -----------1 Stephen 102 Jack 203 BEILL 30(3 行受影响)PK Fname Number----------- ---------- -----------1 Stephen 102 Jack 203 BEILL 30(3 行受影响)
如：有一个表 customer， 现在要用一个procedure过程，插入数据，如果表customer中有条数据就更新，没有就新增。
if OBJECT_ID('customer')is not nulldrop table customergocreate table customer(Customerid int primary key,Firstname varchar(30),Lastname varchar(30),Balance decimal)create procedure usUpsertCustomer (@Customerid int ,@Firstname varchar(30),@Lastname varchar(30),@Balance decimal)asmerge customer c using (select @Customerid as Customerid ,@Firstname as Firstname ,@Lastname as Lastname ,@Balance as Balance)p on c.Customerid=p.Customeridwhen not matched then insert values(p.Customerid,p.Firstname,p.Lastname,p.Balance)when matched then update set c.Customerid=p.Customerid,c.Firstname=p.Firstname,c.Balance=p.Balanceoutput $action,deleted.*,inserted.*;execute usUpsertCustomer 1,'Stephen','Zhou',10/*$action Customerid Firstname Lastname Balance Customerid Firstname Lastname Balance---------- ----------- ------------------------------ ------------------------------ --------------------------------------- ----------- ------------------------------ ------------------------------ ---------------------------------------UPDATE 1 Stephen Zhou 10 1 Stephen Zhou 10(1 行受影响)*/select * from customer/*Customerid Firstname Lastname Balance----------- ------------------------------ ------------------------------ ---------------------------------------1 Stephen Zhou 10(1 行受影响)*/execute usUpsertCustomer 1,'Stephen','Jack',20/*$action Customerid Firstname Lastname Balance Customerid Firstname Lastname Balance---------- ----------- ------------------------------ ------------------------------ --------------------------------------- ----------- ------------------------------ ------------------------------ ---------------------------------------UPDATE 1 Stephen Zhou 10 1 Stephen Zhou 20(1 行受影响)*/select * from customer/*Customerid Firstname Lastname Balance----------- ------------------------------ ------------------------------ ---------------------------------------1 Stephen Zhou 20(1 行受影响)*/
以上基本上介绍了下merge的使用 现在把这个联合 output...into...
create table book (isbn varchar(20) primary key ,price decimal,shelf int)create table weeklychange(isbn varchar(20) primary key,price decimal,shelf int)create table bookhistory([Action] nvarchar(10),newISBN varchar(20),newprice decimal,newshelf int,oldisbn varchar(20),oldprice decimal,oldshelf int,archivedat datetime2)create procedure uspupdatebooks as beginmerge book b using weeklychange w on b.isbn=w.isbn when not matched then insert values (w.isbn,w.price,w.shelf)when matched and (w.isbn!=b.isbn or w.price!=b.price or w.shelf!=b.shelf) then update set b.isbn=w.isbn , b.price=w.price,b.shelf=w.shelfoutput $action,inserted.*,deleted.*,sysdatetime() into bookhistory;endinsert into weeklychange values('SQL程序设计',89,1)select * from bookselect * from weeklychangeselect * from bookhistory/*isbn price shelf-------------------- --------------------------------------- -----------(0 行受影响)isbn price shelf-------------------- --------------------------------------- -----------SQL程序设计 89 1(1 行受影响)Action newISBN newprice newshelf oldisbn oldprice oldshelf archivedat---------- -------------------- --------------------------------------- ----------- -------------------- --------------------------------------- ----------- ----------------------(0 行受影响)*/execute uspupdatebooksselect * from bookselect * from weeklychangeselect * from bookhistory/*isbn price shelf-------------------- --------------------------------------- -----------SQL程序设计 89 1(1 行受影响)isbn price shelf-------------------- --------------------------------------- -----------SQL程序设计 89 1(1 行受影响)Action newISBN newprice newshelf oldisbn oldprice oldshelf archivedat---------- -------------------- --------------------------------------- ----------- -------------------- --------------------------------------- ----------- ----------------------INSERT SQL程序设计 89 1 NULL NULL NULL 2012-12-05 14:19:12.29(1 行受影响)*/
*作者：Stephenzhou(阿蒙)
*日期: 2012.12.5
*Mail:[email protected]
*另外：转载请著名出处。
*博客地址：http://blog.csdn.net/szstephenzhou