T-SQL指令:MERGE的使用SQL 2012版

by adonis 6. 四月 2012 16:21

作    者:楊先民
審    稿:張智凱
文章編號:
出刊日期:


前言
SQL Server 2008之後,提供了新的 SQL指令,名為 Merge,本期將要介紹 Merge的使用方式,以及提供 SQL 2012新範例説明 Merge的使用。

多合一的複合工具:MERGE
就定義上的解釋,MERGE是一種「多合一」的指令,在單一陳述式中執行插入、更新或刪除作業,而這些插入、更新或刪除作業的動作,都會被包在一個交易中執行。而為何平常要使用 MERGE指令,原因在於兩個資料表之間的資料合併工作。
換言之, MERGE並不是一個 SELECT子句,主要功能在資料的合併,特別是你需要指令資料的來源(SOURCE)以及目的(TARGET),將目的資料視為你要合併的資料結果。
然而在看 MERGE之前,我們先看一張圖:

image

圖1:A與B兩個資料表
圖1中,A與B兩個資料表,結構一模一樣,都有編號以及名稱欄位。而你的需求是,將 A與B合併成單一資料表,這時你就可以使用 MERGE。
然而有朋友會說,我可以用 INSERT SELECT指令,將 B資料表的內容匯入到 A,同樣也可以達成目的。
是的,利用 INSERT SELECT同樣能達成 B資料匯入至 A的結果。但如果你仔細看一下圖,你就會發現,雙方都有重複的編號1,名稱為 A的資料。
若是使用 INSERT SELECT指令,則 A的資料表就會變的如圖2一般:

image

圖2:利用 INSERT SELECT匯入資料至A資料表
圖2可以發現,你會產生重複的編號1,名稱A的資料列,這也許不是我們所想要的結果。
換言之,當 A與 B合併在一起時,也許你會希望將重複的 A不與理會,而將不重複的 DE資料匯入至 A資料表中。
然而如果你只是要將 DE的值填入到 A資料表,不用如此麻煩的使用 MERGE,只需要使用下列指令即可:

use tempdb

create table a (a int, b char(1))
go
create table b (a int, b char(1))
go

insert a values (1,'A')
insert a values (2,'B')
insert a values (3,'C')

insert b values (1,'A')
insert b values (4,'D')
insert b values (5,'E')


insert a
select a,b from b where a not in (select a from a)

程式碼列表1:使用 not in指令將資料由 B匯入 A

程式碼列表1中,可以發現,利用 not in指令,將「與A不重複的資料」,由 B資料表匯入至 A資料表達成任務。
那何時需要使用到 MERGE指令呢?
如果把上面的範例改成如圖3一般,即是 MERGE的使用時機:

image

圖3:MERGE的使用時機
對了,若只是將不重複資料匯入,使用 INSERT SELECT指令,搭配 NOT IN就可辦到,而若條件增加為「當資料重複時,刪除 A資料表,而當資料沒有重複時,則將不重複的 B資料新增至 A資料表,則必需使用 MERGE指令達成一個交易同時完成兩個動作。

但若是資料如圖4呢?

image

圖4:增加更新時間的資料表
圖4可以發現, A與 B都各增加了「更新時間」的欄位,而合併的規則我們也稍微修改,「若A與B有相同資料,則 B的更新時間修改至 A資料表資料,若 A與B有不相同資料,則 B資料新增至 A資料表」。

有何發現?

有,這個發現就是,當 A與 B兩個資料表有相同資料時,你會選擇:
1.刪除 A資料表資料
2.更新 A資料表資料
3.不做任何事

但若 A與 B兩個資料表沒有相同的資料,你多半會選擇:
1.將 B資料新增進 A資料表。
2.不做任何事。

此時又有朋友有疑問,為何總是修改 A資料表,而不修改 B資料表?答案是 A資料表稱為目的資料表(TARGET),主要的異動都和它有關,而 B資料表稱為來源資料表(SOURCE),來源是不會,也不能更改的,所以只需要想辦法針對目的資料表異動即可。
MERGE的基本指令
MERGE的語法相當複雜,節錄較簡單的部分如下:

MERGE <target_table> AS 資料表別名
USING <source_table> AS 資料表別名
ON <merge搜尋條件>
[WHEN MATCHED [AND <指定任何有效的搜尋條件>]
THEN <merge目標端有對應的記錄>]

[WHEN NOT MATCHED [BY TARGET] [AND <指定任何有效的搜尋條件>]
THEN <merge目標端沒對應的記錄>]

[WHEN NOT MATCHED BY SOURCE [AND <指定任何有效的搜尋條件>]
THEN <merge目標端有對應的記錄>]

[OUTPUT $action, deleted.*, inserted.*];

在設定 MERGE時,你必需指令哪個資料表為 TARGET,哪個資料表為 SOURCE,就指令而言, MERGE target_table就可看出一切,也就是只有 TARGET資料表會被修改,而 SOURCE資料表不會。
至於 ON後面接搜尋條件,可以想像成是使用 INNER JOIN時,主索引鍵與外來主索引鍵的 JOIN連接,等同於要請 MERGE找尋相同的資料列。

以剛才的資料表 A與 B為例,A為 TARGET而 B為 SOURCE,則前半段的語法應該如下:

MERGE A as target
USING B as source
ON A.a = B.a

如此才是兩個資料表對應相同資料列。

接下來可以設定

[WHEN MATCHED [AND <指定任何有效的搜尋條件>]
THEN <merge目標端有對應的記錄>]

[WHEN NOT MATCHED [BY TARGET] [AND <指定任何有效的搜尋條件>]
THEN <merge目標端沒對應的記錄>]

[WHEN NOT MATCHED BY SOURCE [AND <指定任何有效的搜尋條件>]
THEN <merge目標端有對應的記錄>]

端看你要如何處理合併的資料,此處沒有正確的處理方式,只有幾項原則需要注意:

1.WHEN MATCHED THEN:代表雙方資料表資料有對應,而最多可以寫兩個 WHEN MATCHED,但是若寫兩個 WHEN MATCHED指令,則第一個一定要外加 AND的條件控制,例如:
WHEN MATCHED AND source.name like 'S%' THEN
    XXXXXX
WHEN MATCHED THEN
    OOOOO

換句話說,不能兩個句子都是 WHEN MATCHED

再者,若有兩個 WHEN MATCHED,則一個必需是 UPDATE指令,而另一個則是 DELETE指令,例如:

WHEN MATCHED AND source.name like 'S%' THEN
    UPDATE XXXXXX
WHEN MATCHED THEN
    DELETE OOOOO

至於這點,請回憶前面所提:
當 A與 B兩個資料表有相同資料時,你會選擇:
1.刪除 A資料表資料
2.更新 A資料表資料
3.不做任何事

若不做任何事,則 WHEN MATCHED根本不用寫,但若 MATCHED,則一個選擇更新,一個選擇刪除也是合理的選擇,這也剛好說明為何 WHEN MATCHED最多只能寫兩個,而為何一個只能寫更新,一個只能寫刪除的原因。

2.WHEN NOT MATCHED [BY TARGET] THEN:這是沒有對應到資料的預設情況,也就是 TARGET沒有資料,而 SOURCE有資料。此情況只有將 SOURCE資料新增至 TARGET資料一途,或是不作任何動作(但若原本就不作任何動作,則根本也不用寫這句指令)。

3.WHEN NOT MATCHED BY SOURCE THEN:這也是指沒有對應到資料的情況,但和2.所提相反,也就是 TARGET有資料,但是 SOURCE沒有資料。
當符合這樣的條件時,WHEN NOT MATCHED BY SOURCE最多也可以寫兩個,同樣是一個只能寫更新,而一個只能寫刪除指令,此部分的理由與1.相同。

MERGE的範例
下面有兩個資料表,分別為 Purchases與PurchasesHistory,假設 PurchasesHistory是 TARGET資料表,而 Purchases是 SOURCE資料表,我們的目的是要將來源資料與目的資料合併,資料如下:

USE AdventureWorks2012;
GO
CREATE TABLE dbo.Purchases (
    ProductID int, CustomerID int, PurchaseDate datetime,
    CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO

CREATE TABLE dbo.PurchasesHistory (
    ProductID int, CustomerID int, LastPurchaseDate datetime,
    CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.PurchasesHistory VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO

雙方都有707、870產品編號,顧客編號為11794與15160。
合併策略很簡單,就是「若雙方資料有對應,則 SOURCE的日期修改掉同筆 TARGET的日期」,若資料沒有對應(使用預設的 BY TARGET),則將 SOURCE資料新增至 TARGET。
程式碼如下:

MERGE dbo.PurchasesHistory  AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate);

看似很長,其實照著樣板看下來,MERGE後面接 TARGET (也就是 PurchasesHistory資料表),而 USING後面接 Purchases資料表(也就是 SOURCE),雙方的對應條件是 ProductID與 CustomerID相同才算資料有對應到(到底如何才叫「資料有對應」,完全是你自已的設定)。

若有對應到,則修改 TARGET資料表的 LastPurchaseDate資料。在此注意,UPDATE後無需接資料表名稱,因為一定是 UPDATE TARGET資料表,SOURCE資料表是唯讀的,不能修改。

若沒有對應到(預設是 BY TARGET),則將 SOURCE的資料 INSERT至 TARGET資料表中。此處也無需寫資料表的名稱,因為一定是 INSERT至 TARGET資料表。

至於若你是想寫 WHEN NOT MATCHED BY SOURCE的話,則除了可以 UPDATE TARGET資料之外,也可以刪除資料,刪除的寫法就像下面的寫法一樣:
WHEN NOT MATCHED BY TARGET THEN DELETE
THEN後面直接刪除即可,不需要再設定一些條件之類的,相當簡單。
為了確實了解 merge的更多用法,所以我們稍微把資料表的結構做了一些修改,加上個 type欄位,裡面填入 S或是 M這兩個資料,如下所示:
USE AdventureWorks2012;
GO

drop table purchases
drop table PurchasesHistory


CREATE TABLE dbo.Purchases (
    ProductID int, CustomerID int, PurchaseDate datetime,
    type char(1),
    CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821','S'),
(707, 18178, '20060825','M'),(708, 18529, '20060821','S'),
(711, 11794, '20060821','M'),(711, 19585, '20060822','S');
GO

CREATE TABLE dbo.PurchasesHistory (
    ProductID int, CustomerID int, LastPurchaseDate datetime,
    CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.PurchasesHistory VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
而這裡的 merge 語法,將會設定資料有對應,而且 source的 type為 S,則修改 Date欄位,不然則是把目的資料刪除,語法就會變成如下:
MERGE dbo.PurchasesHistory  AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate,type FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED and source.type='S' THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
when matched then   
    delete
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate);
請注意,這裡常犯的一個錯誤,就是在 on的設定以及 when matched and的地方,常很多人會寫成如下的錯誤指令碼:
MERGE dbo.PurchasesHistory  AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate,type FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID)
WHEN MATCHED and source.type='S' THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
when matched then
    delete
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate);
這裡請特別注意 on的設定只有 target.productid = source.productid,並沒有再加上 and target.customerid = source.customerid,然後在後面的語法就直接加入 when matched and source.type=’S’,這時你若是執行就會發現錯誤訊息:
訊息 8672,層級 16,狀態 1,行 1
MERGE 陳述式嘗試多次 UPDATE 或 DELETE 同一個資料列。這在目標資料列符合多個來源資料列時便會發生。MERGE 陳述式不能多次 UPDATE/DELETE 目標資料表的同一個資料列。請重新定義 ON 子句,以確定目標資料列最多只符合一個來源資料列,或使用 GROUP BY 子句將來源資料列編成群組。
也就是説,這裡 on的對應,實際上就等同於 primary key與 foreign key的對應,要確保資料是能夠找到唯一的一筆記錄,不然都會發生上面的錯誤訊息。
所以整個程式應該改成下面的寫法:
MERGE dbo.PurchasesHistory  AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate,type FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED and source.type='S' THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
when matched then
delete
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate);
如此一來才會有正確的結果。
如果把這個例子的所有可能性都寫成一個 merge的指令,最後的結果可能如下:
MERGE dbo.PurchasesHistory  AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate,type FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED and source.type='S' THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
when matched then
delete
WHEN NOT MATCHED BY TARGET and source.type='S' THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
when not matched by source then
delete;
這裡你會發現,似乎 when not matched by source的指令很少寫到,沒錯!因為目的有資料,而來源對應不到,通常我們不會對此資料進行處理,因為資料「已經在目的端」了,無需再對這個已經在目的端的資料做什麼事情。
另外,要特別注意, merge的指令,陳述句的最後之處要記得加分號(;),不然會出現錯誤訊息,一般的 SQL指令不用特別加分號,只有 merge指令非加不可,不然會出現錯誤訊息。

 

MERGE的更多範例
關於更多 merge範例,我們可以參考 help看看有什麼值得注意的:
USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)   
AS
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.   
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.   
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END
END;
help這個範例值得我們思考,就是以前沒有 merge時,我們對於資料合併的做法,也就是先做資料的 update,如果沒有 update到資料,就進行資料的 insert,也就是採用兩段式的做法。
而改成 merge的寫法,就變成如下:
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN   
        INSERT (UnitMeasureCode, Name)
        VALUES (source.UnitMeasureCode, source.Name)
        OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
這裡稍微注意一下,建立 temp table並不是必需,而是因為此範例需要讓使用者知道有哪些資料被 merge,所以才會使用 temp table(最後使用 output關鍵字將結果匯入到 temp table中)。此外,來源資料表並非一定要是個實體資料表,也可以是個虛擬的,以本例而言,
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
連個資料表的名稱都沒有,直接就給名稱為 source,只是後面定義(UnitMeasureCode,Name)一定要有,不然會出現錯誤訊息,亦即「目前匯入的資料」,檢查是否該更新或是新增,這是個相當好的範例。
再來看第二個範例:

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20050701'
這個範例也相當有意思,因為它的 source資料是使用 join外加 group by的句法,主要是檢查訂購數量是否大於原本的庫存數量,如果訂購數量超過,則刪除存貨資料記錄,若沒有超過則更新存貨庫存資訊。以此例而言, when matched就有兩個,一個要符合 target.Quantity-source.OrderQty<=0的條件,若符合則 delete,「其餘的資料」則是 update。

結論
本期介紹 SQL Server 2008的新 SQL指令,MERGE,並且輔助增加更多的範例以了解這個句子。平常沒事可能不會用到,但若要進行資料合併或是匯入匯出作業時,這功能就蠻需要的,希望各位能夠完全了解它的本意,以用在正確的用途之上。
至於到底實作中會出現幾個 WHEN MATCHED或是 WHEN NOT MATCHED,建議各位可以先用前例圖表的方式先問看看「自己到底要的是什麼」,再開始寫指令,因為 MERGE是將插入、更新或刪除作業包在一個交易中執行,若其中有錯誤都會導致整個交易回復的,不可不注意。

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List