SQL點滴18—SqlServer中的merge操作，相當地風騷

今天在一個存儲過程中看見了merge這個關鍵字，第一個想法是，這個是配置管理中的概念嗎，把相鄰兩次的更改合并到一起。后來在technet上搜索發現別有洞天，原來是另外一個sql關鍵字，t-sql的語法還是相當地豐富的。本篇是一篇學習筆記，沒有什么新意，這里給出technet上的地址連接供大家參考權威：http://technet.microsoft.com/zh-cn/library/bb510625.aspx，這里具體的語法不去深究了，只是把幾個例子實際運行，剖析一番。

使用merge同時執行insert和update操作

我們經常會有這樣的需求，根據某個字段或多個字段查找表中的一行或多行數據，如果查找成功得到匹配項，更新其中的其他一個或多個字段；如果查找失敗則將“某個字段或多個字段”作為新的一行中的數據插入到表中。第一種方法是先更新，然后根據@@rowcount判斷是否有匹配項，如果沒有則插入。先使用下面的代碼創建一個存儲過程。

 1 use AdventureWorks
 2 go
 3 create procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
 4 as
 5 begin
 6 set nocount on;
 7 update Production.UnitMeasure set Name=@Name where UnitMeasureCode=@UnitMeasureCode
 8 if(@@ROWCOUNT=0)
 9   begin
10   insert into Production.UnitMeasure(Name,UnitMeasureCode)values(@Name,@UnitMeasureCode)
11   end
12 end
13 go

記得見過這樣的筆試題目，要求是插入不存在的行，只要把上面語句中的update改成select就可以了，當時沒有寫出來，現在恍然大悟，也許是在考察@@ROWCOUNT的用法吧。這個語句也可以使用merge語句實現。下面我們使用merge關鍵字來修改這個存儲過程。

 1 alter procedure dbo.InsertUnitMeasure @UnitMeasureCode nchar(3),@Name nvarchar(25)
 2 as
 3 begin
 4 set nocount on
 5 merge Production.UnitMeasure as target
 6 using (select @UnitMeasureCode,@Name) as source (UnitMeasureCode,Name)
 7 on (target.UnitMeasureCode=source.UnitMeasureCode)
 8 when matched then update set Name=source.Name
 9 when not matched then insert(UnitMeasureCode,Name)values(source.UnitMeasureCode,Name)
10 output deleted.*,$action,inserted.* into MyTempTable;
11 end
12 go

這個語句使用merge修改存儲過程，這個語句中又出現我不太了解的關鍵字using和$action。Using是用來指定和表InsertUnitMeasure中相匹配的數據源，這里的數據源來自外部輸入，是通過兩個輸入參數得到。$action可能是一個占位符，表示上面的when字句進行的操作。至于inserted.*和deleted.* 就是插入和刪除的數據行了，這個我在其中一篇文章中也提到，他們有點類似類中的this關鍵字，過可以看看：SQL點滴14—編輯數據。注意為了記錄修改的過程我們需要創建一個臨時表#MyTempTable來跟蹤修改過程，所以在調用這個存儲過程之前我們需要新建這個表，語句如下：

 1 create table MyTempTable(
 2 ExistingCode  nchar(3),
 3 ExistingName  nvarchar(50),
 4 ExistingDate  datetime,
 5 ActionTaken   nvarchar(50),
 6 NewCode       nchar(3),
 7 [NewName]     nvarchar(50),      
 8 NewDate       datetime
 9 )
10 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：

圖1

這里雖然三次執行了存儲過程，但是由于第一次和第三次的@UnitMeasureCode的值是相同的’ABC’所以第二次肯定是進行更新操作。所以最后表中新增了兩條記錄。然后使用下面的語句查看記錄表MyTempTable中的跟蹤信息如圖2

圖2

我們可以看到前面兩條語句執行的是插入操作，所以原有的值都是空，因為在插入之前他們還不存在。第三條新型的是更新操作，更新UnitMeasureCode為’ABC’的記錄。

　　

使用merge在單個語句中執行insert和update操作

在AdventureWorks數據庫中有ProductInventory表，存儲的是存貨信息，SalesOrderDetail表中存儲的是訂單信息，現在如果每天減去對SalesOrderDetail表中每種產品所下的訂單數，更新ProductInventory表中的 Quantity列。如果隨著時間推移訂單數導致產品庫存量下降到0或者更少，則從ProductInventory表中刪除該產品對應的行。下面的語句創建一個存儲過程實現上面的邏輯。

 1 CREATE PROCEDURE Production.usp_UpdateInventory
 2     @OrderDate datetime
 3 AS
 4 MERGE Production.ProductInventory AS target
 5 USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
 6     JOIN Sales.SalesOrderHeader AS soh
 7     ON sod.SalesOrderID = soh.SalesOrderID
 8     AND soh.OrderDate = @OrderDate
 9     GROUP BY ProductID) AS source (ProductID, OrderQty)
10 ON (target.ProductID = source.ProductID)
11 WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
12     THEN DELETE
13 WHEN MATCHED 
14     THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
15                     target.ModifiedDate = GETDATE()
16 OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
17     Deleted.Quantity, Deleted.ModifiedDate;
18 GO

這個語句比第一個要復雜一點，注意當匹配成功并且總量小于0的時候直接使用一個delete就可以將此條記錄刪除，output語句直接把操作結果輸出，相當地神奇。最后運行下面的語句得到如圖3的結果。注意這個語句相當于將2003年5月1號的訂單量減去。如果多次運行的話就相當于多減了一次，整個表中數據條數會減少的。

EXECUTE Production.usp_UpdateInventory '20030501'

圖3

借助派生源表，使用merge對目標表執行update和insert操作

這次我們已知有一些表數據，我們要和Sales.SalesReason這個表中的數據做對比，如果和SalesReason表中的Name字段匹配時就更新表中的ReasonType列，如果沒有匹配項的時候就插入這一行新的數據。在這里是使用表值構造函數指定源表的多個行，使用表變量存儲更新記錄，注意表變量的使用范圍。代碼如下：

1 declare @SummaryOfChanges table(Change varchar(20))
2 merge into Sales.SalesReason as target
3 using(values('Recommendation','Other'),('Review','Marketing'),('Internet','Promotion')) as source([NewName],NewReasonType)
4 on target.Name=source.[NewName]
5 when matched then update set ReasonType=source.NewReasonType
6 when not matched by target then insert(Name,ReasonType) values ([NewName],NewReasonType)
7 output $action into @SummaryOfChanges;
8 select Change,COUNT(*) as CountPerChange from @SummaryOfChanges group by Change

執行完上面的語句之后我們得到下面的結果說明執行了2次插入，1次更新，如圖4。那么是不是這樣的呢，我們查看Sales.SalesReason這個表發現原來已經有’Review’這一條數據了，對它執行了更新，剩下的’Recommendation’，’Internet’執行的是插入操作。如果再次執行上面的語句就會得到UPDATE 3這樣的結果，因為已經存在這三條數據了所以都執行UPDATE。

圖4

將merge執行的結果插入到另外一個表中

我們還可以將merge操作得到的結果寫入到另外一個表中，如下的語句將更新的每條數據信息寫入到一個新建的表Production.UpdatedInventory中，代碼如下：

 1 INSERT INTO Production.UpdatedInventory
 2 SELECT ProductID, LocationID, NewQty, PreviousQty 
 3 FROM
 4 (    MERGE Production.ProductInventory AS target
 5      USING (SELECT ProductID, SUM(OrderQty) 
 6             FROM Sales.SalesOrderDetail AS sod
 7             JOIN Sales.SalesOrderHeader AS soh
 8             ON sod.SalesOrderID = soh.SalesOrderID
 9             AND soh.OrderDate BETWEEN '20030701' AND '20030731'
10             GROUP BY ProductID) AS source (ProductID, OrderQty)
11      ON target.ProductID = source.ProductID
12     WHEN MATCHED AND target.Quantity - source.OrderQty >= 0 
13         THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty
14     WHEN MATCHED AND target.Quantity - source.OrderQty <= 0 
15         THEN DELETE
16     OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
17  AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
18 GO

執行這個語句再查詢表得到如下圖5的結果，我們可以看到新的銷售量總是比以前的銷售量要少，因為執行一次就要減去訂單量。

圖5

這里我們只記錄了更新的變化，如果想記錄所有的操作可以去掉最后的一個限制條件WHERE Action = 'UPDATE'，那就要修改記錄表的結構了，這個和第二個例子有些相似，只不過將記錄在實際的表中，而第二個例子僅僅輸出這些操作記錄。

posted @ 2011-06-22 17:11 nd 閱讀(9823) 評論(17) 收藏舉報

刷新頁面返回頂部

SQL點滴18—SqlServer中的merge操作，相當地風騷

公告