今天在一個存儲過程中看見了merge這個關(guān)鍵字，第一個想法是，這個是配置管理中的概念嗎，把相鄰兩次的更改合并到一起。后來在technet上搜索發(fā)現(xiàn)別有洞天，原來是另外一個sql關(guān)鍵字，t-sql的語法還是相當(dāng)?shù)刎S富的。本篇是一篇學(xué)習(xí)筆記，沒有什么新意，這里給出technet上的地址連接供大家參考權(quán)威： http://technet.microsoft.com/zh-cn/library/bb510625.aspx ，這里具體的語法不去深究了，只是把幾個例子實際運(yùn)行，剖析一番。
使用merge同時執(zhí)行insert和update操作
我們經(jīng)常會有這樣的需求，根據(jù)某個字段或多個字段查找表中的一行或多行數(shù)據(jù)，如果查找成功得到匹配項，更新其中的其他一個或多個字段；如果查找失敗則將“某個字段或多個字段”作為新的一行中的數(shù)據(jù)插入到表中。第一種方法是先更新，然后根據(jù)@@rowcount判斷是否有匹配項，如果沒有則插入。先使用下面的 代碼創(chuàng)建一個存儲過程。
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就可以了，當(dāng)時沒有寫出來，現(xiàn)在恍然大悟，也許是在考察@@ROWCOUNT的用法吧。這個語句也可以使用merge語句實現(xiàn)。下面我們使用merge關(guān)鍵字來修改這個存儲過程。
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修改存儲過程，這個語句中又出現(xiàn)我不太了解的關(guān)鍵字using和$action。Using是用來指定和表InsertUnitMeasure中相匹配的數(shù)據(jù)源，這里的數(shù)據(jù)源來自外部輸入，是通過兩個輸入?yún)?shù)得到。$action可能是一個占位符，表示上面的when字句進(jìn)行的操作。至于inserted.*和deleted.* 就是插入和刪除的數(shù)據(jù)行了，這個我在其中一篇文章中也提到，他們有點類似類中的this關(guān)鍵字，過可以看看： SQL點滴14—編輯數(shù)據(jù) 。注意為了記錄修改的過程我們需要創(chuàng)建一個臨時表#MyTempTable來跟蹤修改過程，所以在調(diào)用這個存儲過程之前我們需要新建這個表，語句如下：
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
現(xiàn)在我們來執(zhí)行下面的語句看看有什么樣的結(jié)果：
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 來查看目標(biāo)表中的數(shù)據(jù)變化如圖1：
圖1
這里雖然三次執(zhí)行了存儲過程，但是由于第一次和第三次的@UnitMeasureCode的值是相同的’ABC’所以第二次肯定是進(jìn)行更新操作。所以最后表中新增了兩條記錄。然后使用下面的語句查看記錄表MyTempTable中的跟蹤信息如圖2
圖2
我們可以看到前面兩條語句執(zhí)行的是插入操作，所以原有的值都是空，因為在插入之前他們還不存在。第三條新型的是更新操作，更新UnitMeasureCode為’ABC’的記錄。
?
使用merge在單個語句中執(zhí)行insert和update操作
在AdventureWorks數(shù)據(jù)庫中有ProductInventory表，存儲的是存貨信息，SalesOrderDetail表中存儲的是訂單信息，現(xiàn)在如果每天減去對SalesOrderDetail表中每種產(chǎn)品所下的訂單數(shù)，更新ProductInventory表中的 Quantity列。如果隨著時間推移訂單數(shù)導(dǎo)致產(chǎn)品庫存量下降到0或者更少，則從ProductInventory表中刪除該產(chǎn)品對應(yīng)的行。下面的語句創(chuàng)建一個存儲過程實現(xiàn)上面的邏輯。
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
這個語句比第一個要復(fù)雜一點，注意當(dāng)匹配成功并且總量小于0的時候直接使用一個delete就可以將此條記錄刪除，output語句直接把操作結(jié)果輸出，相當(dāng)?shù)厣衿妗Ｗ詈筮\(yùn)行下面的 語句得到如圖3的結(jié)果。注意這個語句相當(dāng)于將2003年5月1號的訂單量減去。如果多次運(yùn)行的話就相當(dāng)于多減了一次，整個表中數(shù)據(jù)條數(shù)會減少的。
EXECUTE Production.usp_UpdateInventory '20030501'
圖3
借助派生源表，使用merge對目標(biāo)表執(zhí)行update和insert操作
這次我們已知有一些表數(shù)據(jù)，我們要和Sales.SalesReason這個表中的數(shù)據(jù)做對比，如果和SalesReason表中的Name字段匹配時就更新表中的ReasonType列，如果沒有匹配項的時候就插入這一行新的數(shù)據(jù)。在這里是使用表值構(gòu)造函數(shù)指定源表的多個行，使用表變量存儲更新記錄，注意表變量的使用范圍。代碼如下：
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
執(zhí)行完上面的語句之后我們得到下面的結(jié)果說明執(zhí)行了2次插入，1次更新，如圖4。那么是不是這樣的 呢，我們查看Sales.SalesReason這個表發(fā)現(xiàn)原來已經(jīng)有’Review’這一條數(shù)據(jù)了，對它執(zhí)行了更新，剩下的’Recommendation’，’Internet’執(zhí)行的是插入操作。如果再次執(zhí)行上面的語句就會得到UPDATE 3這樣的結(jié)果，因為已經(jīng)存在這三條數(shù)據(jù)了所以都執(zhí)行UPDATE。
圖4
將merge執(zhí)行的結(jié)果插入到另外一個表中
我們還可以將merge操作得到的結(jié)果寫入到另外一個表中，如下的語句將更新的每條數(shù)據(jù)信息寫入到一個新建的表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
執(zhí)行這個語句再查詢表得到如下圖5的結(jié)果，我們可以看到新的銷售量總是比以前的銷售量要少，因為執(zhí)行一次就要減去訂單量。
圖5
這里我們只記錄了更新的變化，如果想記錄所有的操作可以去掉最后的一個限制條件WHERE Action = 'UPDATE'，那就要修改記錄表的結(jié)構(gòu)了，這個和第二個例子有些相似，只不過將記錄在實際的表中，而第二個例子僅僅輸出這些操作記錄。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
