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

來源：懂視網責編：小采時間：2020-11-09 15:34:17

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

SQL點滴18—SqlServer中的merge操作，相當地風騷:今天在一個存儲過程中看見了merge這個關鍵字，第一個想法是，這個是配置管理中的概念嗎，把相鄰兩次的更改合并到一起。后來在technet上搜索發現別有洞天，原來是另外一個sql關鍵字，t-sql的語法還是相當地豐富的。本篇是一篇學習筆記，沒有什么新意，這里給

推薦度：

點擊下載本文 文檔為doc格式

導讀SQL點滴18—SqlServer中的merge操作，相當地風騷:今天在一個存儲過程中看見了merge這個關鍵字，第一個想法是，這個是配置管理中的概念嗎，把相鄰兩次的更改合并到一起。后來在technet上搜索發現別有洞天，原來是另外一個sql關鍵字，t-sql的語法還是相當地豐富的。本篇是一篇學習筆記，沒有什么新意，這里給

今天在一個存儲過程中看見了merge這個關鍵字，第一個想法是，這個是配置管理中的概念嗎，把相鄰兩次的更改合并到一起。后來在technet上搜索發現別有洞天，原來是另外一個sql關鍵字，t-sql的語法還是相當地豐富的。本篇是一篇學習筆記，沒有什么新意，這里給

今天在一個存儲過程中看見了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 createprocedure dbo.InsertUnitMeasure @UnitMeasureCodenchar(3),@Namenvarchar(25)
 4 as
 5 begin
 6 set nocount on;
 7 update Production.UnitMeasure set Name=@Namewhere UnitMeasureCode=@UnitMeasureCode
 8 if(@@ROWCOUNT=0)
 9 begin
10 insertinto Production.UnitMeasure(Name,UnitMeasureCode)values(@Name,@UnitMeasureCode)
11 end
12 end
13 go

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

 1 alterprocedure dbo.InsertUnitMeasure @UnitMeasureCodenchar(3),@Namenvarchar(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 thenupdateset Name=source.Name
 9 whennot matched theninsert(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 createtable 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 CREATEPROCEDURE Production.usp_UpdateInventory
 2 @OrderDatedatetime
 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 GROUPBY ProductID) AS source (ProductID, OrderQty)
10 ON (target.ProductID = source.ProductID)
11 WHEN MATCHED AND target.Quantity - source.OrderQty <=0
12 THENDELETE
13 WHEN MATCHED 
14 THENUPDATESET 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@SummaryOfChangestable(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 thenupdateset ReasonType=source.NewReasonType
6 whennot matched by target theninsert(Name,ReasonType) values ([NewName],NewReasonType)
7 output $action into@SummaryOfChanges;
8 select Change,COUNT(*) as CountPerChange from@SummaryOfChangesgroupby Change

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

圖4

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

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

 1 INSERTINTO 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 GROUPBY ProductID) AS source (ProductID, OrderQty)
11 ON target.ProductID = source.ProductID
12 WHEN MATCHED AND target.Quantity - source.OrderQty >=0 
13 THENUPDATESET target.Quantity = target.Quantity - source.OrderQty
14 WHEN MATCHED AND target.Quantity - source.OrderQty <=0 
15 THENDELETE
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