使用 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 子句是 WHEN MATCHED 子句。
 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 子句是 WHEN NOT MATCHED BY SOURCE 子句。
 TrgEmpID TrgName

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

100      Mary

101      Sara

102      Stefano
 

通過示例輸入流結果可以看出，輸入流結果取決於 WHEN 子句的組合。現在，假定要基於該輸入流對目標表執行以下操作：

僱員 ID 在目標表中不存在並且源僱員名稱以“S”開頭時，插入源表中的行。

目標僱員名稱以“S”開頭並且僱員 ID 在源表中不存在時，刪除目標表中的行。

若要執行這些操作，以下 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 row(s) affected)

通過對 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 row(s) affected)

搜尋條件指導原則
必須正確指定用於匹配源行和目標行的搜尋條件和用於從源或目標中篩選行的其他搜尋條件，以確保獲得正確結果。建議遵循以下指導原則：

在 ON 子句中僅指定這樣的搜尋條件：這些搜尋條件決定源表與目標表中資料的匹配標準。也就是說，僅指定與源表中的對應列進行比較的目標表列。

不包括與其他值（如常量）的比較。

若要從源表或目標表篩選出行，請使用以下方法之一：

在適當的 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 AdventureWorks;
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 表中已處理的訂單，每天更新 AdventureWorks 示例資料庫中的 ProductInventory 表。使用以下 MERGE 語句後，ProductInventory 表的 Quantity 列將通過減去每天為每種產品所下訂單數的方式進行更新。如果某種產品的訂單數導致該產品的庫存下降為 0 或 0 以下，則會從 ProductInventory 表中刪除該產品的行。請注意，源表已經聚合到 ProductID 列。如果不這樣做，源表中會有多個 ProductID 與目標表匹配，從而導致 MERGE 語句返回錯誤。

 
USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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;   

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515893/，如需轉載，請註明出處，否則將追究法律責任。