• 發行項

使用 MERGE 插入、更新，和刪除資料

在 SQL Server 2008 中，您可以使用 MERGE 陳述式，在單一陳述式中執行插入、更新或刪除作業。MERGE 陳述式可讓您將資料來源與目標資料表或檢視表進行聯結，然後根據該聯結的結果，針對目標執行多個動作。例如，您可以使用 MERGE 陳述式來執行下列作業：

  • 有條件地在目標資料表中插入或更新資料列。

    如果此資料列存在目標資料表中，便更新一或多個資料行。否則，便將資料插入新的資料列中。

  • 同步處理兩份資料表。

    根據與資料來源的差異，在目標資料表中插入、更新或刪除資料列。

MERGE 語法包含五個主要子句：

  • MERGE 子句會指定插入、更新或刪除作業的目標資料表或檢視表。

  • USING 子句指定與目標聯結的資料來源。

  • ON 子句會指定聯結條件，這些條件會決定目標與來源在哪裡進行比對。

  • WHEN 子句 (WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE) 會根據 ON 子句的結果及 WHEN 子句中指定的任何其他搜尋條件來指定所要採取的動作。

  • OUTPUT 子句會針對在目標中插入、更新或刪除的每個資料列傳回一個資料列。

如需完整的語法與規則的詳細資訊，請參閱＜MERGE (Transact-SQL)＞。

指定來源和目標搜尋條件。

請務必了解來源和目標資料要如何合併到單一輸入資料流，以及如何使用其他搜尋條件來正確篩選掉不需要的資料列。否則，您可能會使用產生不正確結果的方式來指定其他搜尋條件。

來源中的資料列會根據 ON 子句中指定的聯結述詞與目標中的資料列進行比對。結果為合併的輸入資料流。每個輸入資料列會執行一個插入、更新或刪除作業。根據陳述式中指定的 WHEN 子句而定，此輸入資料列可能會是下列其中一項：

  • 由目標中的一個資料列和來源中的一個資料列所組成的相符配對。這是 WHEN MATCHED 子句的結果。

  • 來源中的資料列，該資料列在目標中沒有對應的資料列。這是 WHEN NOT MATCHED BY TARGET 子句的結果。

  • 目標中的資料列，該資料列在來源中沒有對應的資料列。這是 WHEN NOT MATCHED BY SOURCE 子句的結果。

MERGE 陳述式中指定的 WHEN 子句組合會決定由查詢處理器所實作的聯結類型，而且會影響產生的輸入資料流。為了加以說明，請參考下列來源和目標資料表與資料的範例。

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

下表會列出可能的聯結類型，並指出查詢最佳化工具會在何時實作每一個類型。這個表也會顯示當比對來源與目標資料的搜尋準則為 Source.EmployeeID = Target.EmployeeID 時，範例來源和目標資料表所產生的輸入資料流。

聯結類型

實作

範例輸入資料流結果

INNER JOIN

WHEN MATCHED 子句是唯一指定的 WHEN 子句。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------

NULL     NULL    NULL     NULL

LEFT OUTER JOIN

指定了 WHEN NOT MATCHED BY TARGET 子句，但是未指定 WHEN NOT MATCHED BY SOURCE 子句。不一定會指定 WHEN MATCHED。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------100      Mary    NULL     NULL

101      Sara    NULL     NULL

102      Stefano NULL     NULL

RIGHT OUTER JOIN

指定了 WHEN MATCHED 子句和 WHEN NOT MATCHED BY SOURCE 子句，但是未指定 WHEN NOT MATCHED BY TARGET 子句。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------NULL     NULL    103      Bob

NULL     NULL    104      Steve

FULL OUTER JOIN

指定了 WHEN NOT MATCHED BY TARGET 子句和 WHEN NOT MATCHED BY SOURCE 子句。不一定會指定 WHEN MATCHED。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------100      Mary    NULL     NULL

101      Sara    NULL     NULL

102      Stefano NULL     NULL

NULL     NULL    103      Bob

NULL     NULL    104      Steve

ANTI SEMI JOIN

WHEN NOT MATCHED BY SOURCE 子句是唯一指定的 WHEN 子句。

TrgEmpID TrgName

-------- -------

100      Mary

101      Sara

102      Stefano

範例輸入資料流結果會顯示輸入資料流結果是依據 WHEN 子句的組合。現在假設您想要根據該輸入資料流，在目標資料表上執行下列其中一個動作：

  • 當員工識別碼不存在於目標資料表中，而且來源員工名稱是以 'S' 開頭時，插入來源資料表中的資料列。

  • 當目標員工名稱是以 'S' 開頭，而且員工識別碼不存在於來源資料表中時，刪除目標資料表中的資料列。

若要執行這些動作，將需要下列 WHEN 子句：

  • WHEN NOT MATCHED BY TARGET THEN INSERT

  • WHEN NOT MATCHED BY SOURCE THEN DELETE

如同上表所述，當同時指定了這兩個 WHEN NOT MATCHED 子句時，產生的輸入資料流是來源和目標資料表的完整外部聯結。既然知道了輸入資料流結果，想一想插入、更新和刪除動作將要如何套用到輸入資料流。

如同之前所述，WHEN 子句會根據 ON 子句的結果及 WHEN 子句中指定的任何其他搜尋條件來指定所要採取的動作。在許多案例中，ON 子句內指定的搜尋條件會產生所需的輸入資料流。但是在此範例的情況中，插入和刪除動作需要其他篩選，將受影響的資料列限制為員工名稱以 'S' 開頭的資料列。在下列範例中，篩選條件會套用到 WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE。陳述式中的輸出顯示會更正、插入或刪除輸入資料流中的預期資料列。

-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

以下是 OUTPUT 子句的結果。

$action   EmployeeID  EmployeeName EmployeeID  EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE    NULL        NULL         101         Sara

DELETE    NULL        NULL         102         Stefano

INSERT    104         Steve        NULL        NULL

(3 個資料列受到影響)

在程序的早期藉由對 ON 子句指定其他搜尋條件 (例如，藉由指定 ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') 來減少輸入資料流中的資料列數時，可能會改善查詢效能。但是，這樣做可能會產生非預期且不正確的結果。因為 ON 子句中指定的其他搜尋條件不會用來比對來源和目標資料，所以它們可能會套用錯誤。

下列範例示範為何會發生不正確的結果。用來比對來源和目標資料表的搜尋條件以及用來篩選資料列的其他搜尋條件都會在 ON 子句中指定。因為不需要其他搜尋條件也可判斷來源和目標是否相符，所以插入和更新動作會套用到所有輸入資料列。實際上，將會忽略篩選條件 EmployeeName LIKE 'S%'。當執行此陳述式時，inserted 和 deleted 資料表的輸出會顯示兩個資料列被錯誤修改：從目標資料表中錯誤地刪除了 Mary，而且錯誤地插入了 Bob。

-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%' 
    AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO

以下是 OUTPUT 子句的結果。

$action   EmployeeID  EmployeeName EmployeeID  EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE    NULL        NULL         100         Mary

DELETE    NULL        NULL         101         Sara

DELETE    NULL        NULL         102         Stefano

INSERT    103         Bob          NULL        NULL

INSERT    104         Steve        NULL        NULL

(5 個資料列受到影響)

搜尋條件指導方針

用來比對來源和目標資料列的搜尋條件以及用來篩選來源或目標中之資料列的其他搜尋條件都必須指定正確，才能確保可取得正確的結果。我們建議您遵循下列指導方針：

  • 在 ON <merge_search_condition> 子句中只指定可決定用來比對來源和目標資料表資料之準則的搜尋條件。也就是說，只從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。

  • 請勿包含與其他值 (如常數) 的比較。

若要從來源或目標資料表中篩選掉資料列，請使用下列其中一個方法：

  • 在適當的 WHEN 子句中指定資料列篩選的搜尋條件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...。

  • 在來源或目標上定義一個可傳回篩選過之資料列的檢視表，並將此檢視表當做來源或目標資料表來參考。如果此檢視表定義在目標資料表上，則對其進行的任何動作都必須滿足更新檢視表的條件。如需有關使用檢視表來更新資料的詳細資訊，請參閱＜透過檢視修改資料＞。

  • 使用 WITH <通用資料表運算式> 子句，從來源或目標資料表中篩選掉資料列。這個方法類似於在 ON 子句中指定其他搜尋條件，而且可能會產生不正確的結果。我們建議您最好避免使用這個方法，或是在實作這個方法之前先徹底加以測試。

範例

A. 使用簡單 MERGE 陳述式來執行 INSERT 和 UPDATE 作業

假設您在資料倉儲資料庫中有一份 FactBuyingHabits 資料表，用以追蹤每位客戶購買某件特定產品的最後日期。而位於 OLTP 資料庫中的第二份資料表 Purchases 記錄了特定一週所購買的產品。您想要從 Purchases 資料表中，每週將特定客戶從未購買之產品的資料列加入至 FactBuyingHabits 資料表。而針對客戶已經購買過之產品的資料列，您只想要在 FactBuyingHabits 資料表中更新購買日期。您可以使用 MERGE，在單一陳述式中執行這些插入和更新作業。

下列範例會先建立 Purchases 和 FactBuyingHabits 資料表，並載入某些範例資料。針對聯結索引鍵建立 UNIQUE 索引時，MERGE 陳述式的效能會得到改進，同時會針對兩份資料表中的 ProductID 資料行建立 PRIMARY KEY 條件約束，藉以建立叢集索引。

在此範例中，Purchases 包含 2006 年 8 月 21 日該週的採購項目，而 FactBuyingHabits 則包含前一週的採購項目。通常此資料表中會填入更早以前的資料列。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL 
    DROP TABLE dbo.Purchases;
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
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL 
    DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
    ProductID int, CustomerID int, LastPurchaseDate datetime, 
    CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits 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

現在，資料表中填入了下列資料：

dbo.Purchases

ProductID   CustomerID  PurchaseDate

----------- ----------- -----------------------

707         11794       2006-08-20 00:00:00.000

707         15160       2006-08-25 00:00:00.000

708         18529       2006-08-21 00:00:00.000

711         11794       2006-08-20 00:00:00.000

711         19585       2006-08-22 00:00:00.000

712         14680       2006-08-26 00:00:00.000

712         21524       2006-08-26 00:00:00.000

712         19072       2006-08-20 00:00:00.000

870         15160       2006-08-23 00:00:00.000

870         11927       2006-08-24 00:00:00.000

870         18749       2006-08-25 00:00:00.000

dbo.FactBuyingHabits

ProductID   CustomerID  LastPurchaseDate

----------- ----------- -----------------------

707         11794       2006-08-14 00:00:00.000

707         18178       2006-08-18 00:00:00.000

864         14114       2006-08-18 00:00:00.000

866         13350       2006-08-18 00:00:00.000

866         20201       2006-08-15 00:00:00.000

867         20201       2006-08-14 00:00:00.000

869         19893       2006-08-15 00:00:00.000

870         17151       2006-08-18 00:00:00.000

870         15160       2006-08-17 00:00:00.000

871         21717       2006-08-17 00:00:00.000

871         21163       2006-08-15 00:00:00.000

871         13350       2006-08-15 00:00:00.000

873         23381       2006-08-15 00:00:00.000

請注意，有兩個產品-客戶資料列通用於這兩份資料表：在當週與前一週中，客戶 11794 都購買了產品 707，而且客戶 15160 也在當週與前一週都購買了產品 870。針對這些資料列，我們使用 WHEN MATCHED THEN 子句，以 Purchases 中記錄這些採購項目的日期，更新 FactBuyingHabits。我們使用 WHEN NOT MATCHED THEN 子句，將所有其他資料列插入 FactBuyingHabits 中。

MERGE dbo.FactBuyingHabits 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)
OUTPUT $action, Inserted.*, Deleted.*;

B. 執行 UPDATE 和 DELETE 作業

下列範例使用 MERGE，根據在 SalesOrderDetail 資料表中處理的順序，每日更新 AdventureWorks2008R2 範例資料庫中的 ProductInventory 資料表。使用下列 MERGE 陳述式，ProductInventory 資料表中的 Quantity 資料行會藉著減去每個產品每日所下的訂單數量來進行更新。如果產品的訂單數量使它的存貨降為 0 或以下，該產品的資料列就會從 ProductInventory 資料表中刪除。請注意，來源資料表的彙總是在 ProductID 資料行中進行。如果這個步驟沒有完成，在來源資料表中可能至少會有一個 ProductID 與目標資料表相符，因而造成 MERGE 陳述式傳回錯誤。

USE AdventureWorks2008R2;
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 '20030501'

C. 執行 INSERT、UPDATE 和 DELETE 作業

下列範例會使用 MERGE，根據與資料來源的差異，在目標資料表中插入、更新或刪除資料列。假設有間小公司，它擁有五個部門，每個部門有一位部門經理。公司決定要重新組織這些部門。若要在目標資料表 dbo.Departments 中實作重新組織的結果，MERGE 陳述式必須實作下列變更：

  • 某些現有的部門將不會有所變更。

  • 某些現有的部門會有新的經理。

  • 某些部門是新建立的。

  • 某些部門在重新組織後，就不再存在。

下列程式碼會建立目標資料表 dbo.Departments 並填入經理。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments 
    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'), 
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer');

對部門所做的組織變更會儲存在來源資料表 dbo.Departments_delta 中。下列程式碼會建立這份資料表，並在其中填入資料：

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES 
    (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
    (3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'), 
    (6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO

最後，為了在目標資料表中反映公司的重新組織，下列程式碼會使用 MERGE 陳述式來比較來源資料表 dbo.Departments_delta 與目標資料表 dbo.Departments。這項比較的搜尋條件會定義在陳述式的 ON 子句中。系統會根據比較的結果，採取下列動作。

  • 在兩份資料表中都存在的部門會在目標資料表中以資料表 Departments 中的新名稱、新經理或兩者更新。如果沒有任何變更，也不會有任何更新。這項動作會在 WHEN MATCHED THEN 子句中完成。

  • 所有不存在於 Departments 但卻存在 Departments_delta 中的部門都會插入 Departments 中。這項動作會在 WHEN NOT MATCHED THEN 子句中完成。

  • 所有不存在於來源資料表 Departments_delta 但卻存在 Departments 中的部門都會從 Departments 中刪除。這項動作會在 WHEN NOT MATCHED BY SOURCE THEN 子句中完成。

MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
    INSERT (DeptID, DeptName, Manager)
        VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, 
       inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName, 
       inserted.Manager AS SourceManager, 
       deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName, 
       deleted.Manager AS TargetManager;