MERGE を使用したデータの挿入、更新、および削除

ソース行と対象行の照合に使用する検索条件、およびソースまたは対象のいずれかの行のフィルタ選択に使用する追加の検索条件は、正しい結果が得られるように正しく指定する必要があります。以下のガイドラインに従うことをお勧めします。

• ON <merge_search_condition> 句で、ソース テーブルと対象テーブルのデータを照合する基準を判断する検索条件のみを指定します。つまり、対象テーブルのうち、ソース テーブル内の対応する列と比較する列のみを指定します。

• 定数などのその他の値との比較は指定しないでください。

ソース テーブルまたは対象テーブルから行を除外するには、次のいずれかの方法を使用します。

• 適切な WHEN 句で、行をフィルタ選択するための検索条件を指定します。たとえば、WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT... のようにします。

• フィルタ選択された行を返すソースまたは対象のビューを定義して、そのビューをソース テーブルまたは対象テーブルとして参照します。ビューが対象テーブルに対して定義されている場合、これに対するアクションはビューの更新条件を満たす必要があります。ビューを使用してデータを更新する方法の詳細については、「ビューを使用したデータ変更」を参照してください。

• WITH <共通テーブル式> 句を使用して、ソース テーブルまたは対象テーブルから行を除外します。この方法は、ON 句で追加の検索条件を指定する方法に似ており、不適切な結果が返される可能性があります。この方法を使用しないようにするか、十分にテストしてから実装することをお勧めします。

データ ウェアハウスのデータベースに、各顧客が特定の製品を購入した最新の日付を追跡する FactBuyingHabits テーブルがあるとします。OLTP データベースの 2 番目のテーブル Purchases は、指定された週の購入を記録します。毎週、特定の顧客が購入したことのない製品の行を Purchases テーブルから FactBuyingHabits テーブルに追加したいと考えています。以前に購入したことのある製品を購入した顧客の行については、FactBuyingHabits テーブルの購入日を単に更新します。これらの挿入操作と更新操作は、MERGE を使用して単一のステートメントで実行できます。

次の例では、まず Purchases テーブルと FactBuyingHabits テーブルを作成し、サンプル データを挿入します。結合キーにインデックスを作成すると、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

両方のテーブルに共通する 2 つの製品 - 顧客行が存在します。顧客 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.*; 

次の例では、MERGE を使用して、SalesOrderDetail テーブルで処理される注文に基づいて、AdventureWorks サンプル データベース内の ProductInventory テーブルを毎日更新します。次の MERGE ステートメントを使用して、各製品のその日の注文数を差し引くことで、ProductInventory テーブルの Quantity 列を更新します。製品の注文数によって、製品の在庫が 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'

次の例では、MERGE を使用して、ソース データとの違いに基づいて、対象テーブルに対する行の挿入、更新、または削除を実行します。5 つの部署があり、各部署に管理者が配置されている小企業を想定してください。この会社では、部署を再編成することにしました。再編成の結果を対象テーブル 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;