[アーティクル]
10/31/2012

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 句だけである場合。	TrgEmpID TrgName SrcEmpID SrcName -------- ------- -------- ------- NULL NULL NULL NULL
LEFT OUTER JOIN	WHEN NOT MATCHED BY TARGET 句が指定されているが、WHEN NOT MATCHED BY SOURCE 句は指定されていない場合。WHEN MATCHED が指定されているかどうかは関係ありません。	TrgEmpID TrgName SrcEmpID SrcName -------- ------- -------- -------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 句は指定されていない場合。	TrgEmpID TrgName SrcEmpID SrcName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve
FULL OUTER JOIN	WHEN NOT MATCHED BY TARGET 句と WHEN NOT MATCHED BY SOURCE 句が指定されている場合。WHEN MATCHED が指定されているかどうかは関係ありません。	TrgEmpID TrgName SrcEmpID SrcName -------- ------- -------- -------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 行処理されました)

検索条件のガイドライン

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

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 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

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

次の方法で共有