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

SQL Server 2008 では、MERGE ステートメントを使用して、単一のステートメントで挿入、更新、または削除の各操作を実行できます。MERGE ステートメントを使用すると、データ ソースを対象のテーブルまたはビューと結合し、その結合結果に基づいて複数のアクションを対象に対して実行することができます。たとえば、MERGE ステートメントを使用すると、次の操作を実行できます。

  • 対象テーブルに対して、条件付きの行の挿入または更新を実行する。

    対象テーブルに行が存在する場合は、1 つまたは複数の列を更新します。行が存在しない場合は、新しい行にデータを挿入します。

  • 2 つのテーブルを同期する。

    ソース データとの違いに基づいて、対象テーブルに対する行の挿入、更新、または削除を実行します。

MERGE の構文は、次の 5 つの主要な句で構成されています。

  • MERGE 句は、挿入、更新、または削除の各操作の対象となるテーブルまたはビューを指定します。

  • USING 句は、対象と結合されるデータ ソースを指定します。

  • ON 句は、対象とソースが一致しているかどうか判断する結合条件を指定します。

  • WHEN 句 (WHEN MATCHED、WHEN NOT MATCHED BY TARGET、および WHEN NOT MATCHED BY SOURCE) は、ON 句の結果、および WHEN 句で指定した追加の検索条件の結果に基づいて実行する操作を指定します。

  • OUTPUT 句は、挿入、更新、または削除される対象の行ごとに 1 行を返します。

構文と規則の詳細については、「MERGE (Transact-SQL)」を参照してください。

ソースと対象の検索条件の指定

ソース データと対象データが単一の入力ストリームにマージされるしくみと、追加の検索条件を使用して不要な行を正しく除外する方法を理解しておくことが重要です。これらを理解しておかないと、追加の検索条件を指定した場合に不適切な結果が返される可能性があります。

ソースの行は、ON 句で指定した結合述語に基づいて対象の行と照合されます。結果は、組み合わされた入力ストリームになります。入力行ごとに挿入、更新、削除のいずれかの操作が 1 回実行されます。ステートメントで指定した 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 句の組み合わせによって異なります。ここで、その入力ストリームに基づいて次の操作を対象テーブルに対して実行するとします。

  • ソース テーブル内の "S" で始まる従業員名に対応する従業員 ID が対象テーブルに存在しない場合、ソース テーブルからその行を挿入する。

  • 対象テーブル内の "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 テーブルの出力で 2 つの行が不適切に変更されていることがわかります。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 <merge_search_condition> 句で、ソース テーブルと対象テーブルのデータを照合する基準を判断する検索条件のみを指定します。つまり、対象テーブルのうち、ソース テーブル内の対応する列と比較する列のみを指定します。

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

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

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

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

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

A. 単純な MERGE ステートメントを使用して INSERT および UPDATE 操作を実行する

データ ウェアハウスのデータベースに、各顧客が特定の製品を購入した最新の日付を追跡する 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.*; 

B. UPDATE および DELETE 操作を実行する

次の例では、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'

C. INSERT、UPDATE、および DELETE 操作を実行する

次の例では、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;