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