MERGE (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics

MERGE ステートメントは、ソース テーブルとの結合結果から、挿入、更新、または削除操作を対象テーブルに対して実行します。 たとえば、他のテーブルとの違いに基づいて、あるテーブル内の行を挿入、更新、または削除することにより、2 つのテーブルを同期します。

Note

Azure Synapse Analytics に固有の MERGE 情報については、バージョンの選択を Azure Synapse Analytics に変更します。

ヒント

説明した MERGE ステートメントの条件付きの動作は、一致する特性が 2 つのテーブルで複雑に組み合わされている場合に最適です。 たとえば、存在しない場合は行を挿入し、一致する場合は行を更新します。 別のテーブルの行に基づいて 1 つのテーブルを更新するだけで、INSERT、UPDATE、および DELETE ステートメントのパフォーマンスとスケーラビリティが向上します。 次に例を示します。

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

Transact-SQL 構文表記規則

構文

SQL Server と Azure SQL Database の構文:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

引数

WITH <common_table_expression>

MERGE ステートメントのスコープ内で定義された、一時的な名前付き結果セットまたはビュー (共通テーブル式とも呼ばれます) を指定します。 結果セットは単純なクエリから派生し、MERGE ステートメントで参照されます。 詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。

TOP ( expression ) [ PERCENT ]

影響を受ける行の数またはパーセンテージを指定します。 expression には、行数または行のパーセンテージを指定できます。 TOP 式で参照される行は順序付けされません。 詳しくは、「TOP (Transact-SQL)」をご覧ください。

TOP 句は、ソース テーブル全体とターゲット テーブル全体が結合され、挿入、更新、または削除操作の対象とならない結合された行が削除された後に適用されます。 さらに、TOP 句は、結合された行の数を、指定した値に減らします。 これらの操作 (挿入、更新、または削除) は、残りの結合された行に対して、順不同の方法で適用されます。 つまり、WHEN 句に定義された操作に行が割り当てられる順序は決まっていません。 たとえば、TOP (10) を指定すると、10 行が影響を受けます。 これらの行では、7 行が更新されて 3 行が挿入される場合も、1 行が削除され、5 行が更新され、4 行が挿入される場合もあります。

ソース テーブルに対するフィルターがないと、MERGE ステートメントでは、ターゲット テーブルのテーブル スキャンまたはクラスター化インデックス スキャンだけでなく、ソース テーブルに対してテーブル スキャンまたはクラスター化インデックス スキャンが実行される場合があります。 したがって、TOP 句を使用し、複数のバッチを作成して大きなテーブルを変更するときも、I/O パフォーマンスが影響を受ける場合があります。 このシナリオでは、連続するすべてのバッチで確実に新しい行が対象になっていることが重要です。

database_name

target_table があるデータベースの名前です。

schema_name

target_table が属しているスキーマの名前です。

target_table

<clause_search_condition> に基づいて <table_source> のデータ行が照合されるテーブルまたはビューです。 target_table は、MERGE ステートメントの WHEN 句で指定された挿入、更新、削除のいずれかの操作の対象です。

target_table がビューの場合、これに対するアクションはビューの更新条件を満たす必要があります。 詳細については、「ビューを使用したデータ変更」を参照してください。

target_table にリモート テーブルを指定することはできません。 target_table にルールを定義することはできません。target_table はメモリ最適化テーブルになることはできません。

ヒントは、<merge_hint> として指定できます。

[ AS ] table_alias

target_table のテーブルを参照するための代替名です。

USING <table_source>

<merge_search_condition> に基づいて target_table 内のデータ行と照合するデータ ソースを指定します。 この照合結果によって、MERGE ステートメントの WHEN 句で実行される操作が決まります。 <table_source> には、リモート テーブルを指定することも、リモート テーブルにアクセスする派生テーブルを指定することもできます。

<table_source> には派生テーブルを指定できます。このテーブルでは、複数の行を指定してテーブルを作成するために Transact-SQL テーブル値コンストラクターが使用されます。

[ AS ] table_alias

table_source のテーブルを参照するための代替名です。

この句の構文および引数の詳細については、「FROM (Transact-SQL)」を参照してください。

ON <merge_search_condition>

<table_source> と target_table を結合するための一致箇所を特定する条件を指定します。

注意事項

照合目的で使用する対象テーブルの列だけを指定することが重要です。 つまり、対象テーブルのうち、ソース テーブルの対応する列と比較する列を指定します。 AND NOT target_table.column_x = value と指定するなど、ON 句で対象テーブル内の行にフィルターを適用することによって、クエリ パフォーマンスを向上させようとしないでください。 この場合、予期しない無効な結果が返される可能性があります。

WHEN MATCHED THEN <merge_matched>

<table_source> ON <merge_search_condition> で返される行に一致し、追加の検索条件を満たす *target_table のすべての行を、<merge_matched> 句に従って更新または削除するように指定します。

MERGE ステートメントには、最大 2 つの WHEN MATCHED 句を指定できます。 句を 2 つ指定する場合、最初の句は AND <search_condition> 句と共に使用する必要があります。 任意の行に対し、最初の WHEN MATCHED 句が適用されなかった場合にのみ、2 番目の WHEN MATCHED 句が適用されます。 WHEN MATCHED 句が 2 つある場合は、一方で UPDATE 操作を、もう一方で DELETE 操作を指定する必要があります。 <merge_matched> 句で UPDATE が指定されており、<merge_search_condition> に基づいて <table_source> の複数の行が target_table の 1 つの行に一致する場合、SQL Server からエラーが返されます。 MERGE ステートメントで、同じ行を複数回更新することや、同じ行の更新と削除を行うことはできません。

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

<table_source> ON <merge_search_condition> で返される行のうち、target_table 内の行とは一致しないが、追加の検索条件 (存在する場合) は満たす行ごとに、target_tableに 1 行を挿入するように指定します。 挿入する値は、<merge_not_matched> 句で指定します。 MERGE ステートメントに指定できる WHEN NOT MATCHED [ BY TARGET ] 句は 1 つだけです。

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

<table_source> ON <merge_search_condition> で返される行に一致せず、追加の検索条件を満たす *target_table のすべての行を、<merge_matched> 句に従って更新または削除するように指定します。

MERGE ステートメントには、最大 2 つの WHEN NOT MATCHED BY SOURCE 句を指定できます。 句を 2 つ指定する場合、最初の句は AND <clause_search_condition> 句と共に使用する必要があります。 任意の行に対し、最初の WHEN NOT MATCHED BY SOURCE 句が適用されなかった場合にのみ、2 番目の WHEN NOT MATCHED BY SOURCE 句が適用されます。 WHEN NOT MATCHED BY SOURCE 句が 2 つある場合は、一方で UPDATE 操作を、もう一方で DELETE 操作を指定する必要があります。 <clause_search_condition> では対象テーブルの列のみを参照できます。

<table_source> から返される行がない場合、ソース テーブルの列にアクセスすることはできません。 <merge_matched> 句に指定した更新操作または削除操作でソース テーブルの列を参照していると、エラー 207 (無効な列名) が返されます。 たとえば、ソース テーブルの WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 にアクセスできないために、Col1 句によってステートメントが失敗する可能性があります。

AND <clause_search_condition>

任意の有効な検索条件を指定します。 詳細については、「検索条件 (Transact-SQL)」を参照してください。

<table_hint_limited>

MERGE ステートメントによって実行される挿入、更新、削除の各操作に対し、対象テーブルに適用される 1 つ以上のテーブル ヒントを指定します。 キーワード WITH とかっこが必要です。

NOLOCK および READUNCOMMITTED は指定できません。 テーブル ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

INSERT ステートメントの対象であるテーブルに対して TABLOCK ヒントを指定すると、TABLOCKX ヒントを指定した場合と同じ効果を得られます。 テーブルに対して、排他ロックが取得されます。 FORCESEEK を指定すると、ソース テーブルに結合された対象テーブルの暗黙のインスタンスに対して適用されます。

注意事項

WHEN NOT MATCHED [ BY TARGET ] THEN INSERT に READPAST を指定すると、UNIQUE 制約に違反する INSERT 操作が発生する可能性があります。

INDEX ( index_val [ ,...n ] )

ソース テーブルとの暗黙の結合を実行するための、対象テーブルの 1 つ以上のインデックスの名前または ID を指定します。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

<output_clause>

target_table 内の更新、挿入、または削除される行ごとに 1 行を返します。この場合、特定の順序はありません。 $action は、OUTPUT 句に指定することができます。 $action は、それぞれの行に対して実行されたアクションに従って、次のいずれかの値をその行について返す nvarchar(10) 型の列です: INSERT、UPDATE、または DELETE。 OUTPUT 句は、MERGE の影響を受ける行を照会またはカウントする場合に推奨される方法です。 この句の引数と動作の詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

OPTION ( <query_hint> [ ,...n ] )

オプティマイザー ヒントを使用して、データベース エンジンがステートメントを処理する方法をカスタマイズすることを指定します。 詳細については、「ヒント (Transact-SQL) - クエリ」を参照してください

<merge_matched>

<table_source> ON <merge_search_condition> で返される行には一致しないが、追加の検索条件は満たす target_table のすべての行に対して適用する更新操作または削除操作を指定します。

UPDATE SET <set_clause>

対象テーブル内で更新する列名または変数名の一覧と、それらの更新に使用する値を指定します。

この句の引数について詳しくは、「UPDATE (Transact-SQL)」をご覧ください。 列と同じ値を変数に設定することはできません。

DELETE

target_table 内の行に一致する行を削除するように指定します。

<merge_not_matched>

対象テーブルに挿入する値を指定します。

(column_list)

対象テーブルのデータを挿入する 1 つ以上の列で構成されるリストを指定します。 列は 1 部構成の名前で指定する必要があります。そうしないと MERGE ステートメントが失敗します。 column_list はかっこで囲み、コンマで区切る必要があります。

VALUES ( values_list)

対象テーブルに挿入する値を返す定数、変数、または式を、コンマ区切りのリストで指定します。 式に EXECUTE ステートメントを含めることはできません。

DEFAULT VALUES

挿入される行が、各列に対して定義されている既定値で構成されることを指定します。

この句について詳しくは、「INSERT (Transact-SQL)」をご覧ください。

<search_condition>

<merge_search_condition> または <clause_search_condition> を指定する検索条件を指定します。 この句の引数について詳しくは、「検索条件 (Transact-SQL)」をご覧ください。

<グラフの検索パターン>

グラフの一致パターンを指定します。 この句の引数の詳細については、「MATCH (Transact-SQL)」を参照してください

解説

3 つの MATCHED 句のうち、少なくとも 1 つは指定する必要があります。これらの句は、任意の順序で指定できます。 1 つの MATCHED 句で 1 つの変数を複数回更新することはできません。

MERGE ステートメントによって対象テーブルに指定された挿入、更新、削除の各操作は、連鎖参照整合性制約など、定義されている制約の制限を受けます。 対象テーブルの一意インデックスで IGNORE_DUP_KEY が ON に設定されていると、MERGE ではこの設定が無視されます。

MERGE ステートメントでは、ステートメントのターミネータとしてセミコロン (;) が必要です。 MERGE ステートメントにターミネータを付けずに実行すると、エラー 10713 が生成されます。

MERGE の後に @@ROWCOUNT (Transact-SQL) を使用すると、クライアントに対して挿入、更新、および削除された行の合計数が返されます。

データベースの互換性レベルが 100 以上に設定されている場合、MERGE は完全に予約されたキーワードです。 MERGE ステートメントはデータベースの互換性レベルが 90 と 100 の両方で使用できますが、データベースの互換性レベルが 90 に設定されている場合、MERGE キーワードは完全には予約されません。

注意事項

キュー更新レプリケーションを使う場合は、MERGE ステートメントを使用しないでください。 MERGE とキュー更新トリガーには互換性がありません。 MERGE ステートメントは、挿入ステートメントまたは更新ステートメントと置き換えてください。

トリガーの実装

MERGE ステートメントに指定された挿入、更新、削除の各操作に対し、対象テーブルで定義された対応する AFTER トリガーが SQL Server によって起動されますが、どの操作に対するトリガーからどのような順序で起動されるかは決まっていません。 同じ操作に対して定義された複数のトリガーは、指定した順序に従います。 トリガー起動順序の設定の詳細については、「最初と最後のトリガーの指定」を参照してください。

MERGE ステートメントによって実行される挿入、更新、削除のいずれかの操作に対して、有効な INSTEAD OF トリガーが対象テーブルに定義されている場合、MERGE ステートメントに指定されたすべての操作に有効な INSTEAD OF トリガーを定義する必要があります。

INSTEAD OF UPDATE トリガーや INSTEAD OF DELETE トリガーが target_table に定義されている場合、更新操作や削除操作は実行されません。 代わりにトリガーが起動され、inserted テーブルと deleted テーブルに適切なデータが設定されます。

INSTEAD OF INSERT トリガーが target_table に定義されている場合、挿入操作は実行されません。 代わりに、テーブルに適切なデータが設定されます。

Note

個別の INSERT、UPDATE、DELETE ステートメントとは異なり、トリガー内の @@ROWCOUNT によって反映される行数は多くなる可能性があります。 AFTER トリガー内の @@ROWCOUNT (トリガーで取り込まれるデータ変更ステートメントに関係なく) には、MERGE の影響を受ける行の総数が反映されます。 たとえば、MERGE ステートメントで 1 つの行を挿入し、1 つの行を更新し、1 つの行を削除した場合、トリガーが INSERT ステートメントに対してのみ宣言されている場合でも、AFTER トリガーでは @@ROWCOUNT が 3 になります。

アクセス許可

ソース テーブルに対する SELECT 権限と、対象テーブルに対する INSERT、UPDATE、または DELETE 権限が必要です。 詳しくは、「SELECT」、「INSERT」、「UPDATE」、「DELETE」の各記事の「権限」をご覧ください。

インデックスに関するベスト プラクティス

MERGE ステートメントを使用すると、個々の DML ステートメントを単一のステートメントに置き換えることができます。 これにより、操作が単一のステートメント内で実行されてソース テーブルと対象テーブルのデータの処理回数が最小限に抑えられるので、クエリのパフォーマンスが向上します。 ただし、パフォーマンスが向上するかどうかは、インデックスが正しいか、結合が存在するかなど、いくつかの考慮事項によって決まります。

MERGE ステートメントのパフォーマンスを向上させるには、次のインデックスのガイドラインに従うことをお勧めします。

• MERGE のソースとターゲット間の結合を容易にする、次のインデックスを作成します。
  • ターゲット テーブルへの結合ロジックに対応するキーを持つソース テーブルの結合列に、インデックスを作成します。 可能な場合は、これを一意にする必要があります。
  • また、ターゲット テーブルの結合列にインデックスを作成します。 可能な場合は、これを一意のクラスター化インデックスにする必要があります。
  • これら 2 つのインデックスにより、テーブル内のデータが並べ替えられ、一意性によって比較のパフォーマンスが向上します。 クエリ オプティマイザーで重複行を検索して更新するための追加の検証処理を実行する必要がなく、追加の並べ替え操作が不要になるため、クエリのパフォーマンスが向上します。
• MERGE ステートメントのターゲットとして、任意の形式の列ストア インデックスを含むテーブルを使用しないようにしてください。 UPDATE と同様に、列ストア インデックスよりもパフォーマンスが向上する可能性があります。その場合、ステージングされた行ストア テーブルを更新してから、UPDATE または MERGE ではなく、バッチ処理された DELETE と INSERT を実行します。

MERGE のコンカレンシーに関する考慮事項

ロックの点では、MERGE は、個別の連続する INSERT、UPDATE、および DELETE ステートメントとは異なります。 MERGE では引き続き INSERT、UPDATE、および DELETE 操作を実行しますが、異なるロック メカニズムを使用します。 一部のアプリケーションのニーズに合わせて、個別の INSERT、UPDATE、および DELETE ステートメントを記述する方が効率的である可能性があります。 大規模な MERGE では、複雑なコンカレンシーの問題が発生したり、高度なトラブルシューティングが必要になる可能性があります。 そのため、運用環境にデプロイする前に、MERGE ステートメントを徹底的にテストするように計画してください。

MERGE ステートメントは、次のシナリオ (ただし、これらに限定されない) での個別の INSERT、UPDATE、および DELETE 操作に適しています。

• 大量の行数を含む ETL 操作は、他の同時実行操作が予期されていない期間中に実行されます。 高いコンカレンシーが予期される場合、個別の INSERT、UPDATE、および DELETE ロジックでは、ブロックが少なくなり、MERGE ステートメントよりもパフォーマンスが向上する可能性があります。
• 小さな行数および長期間実行される可能性が低いトランザクションに関連する複雑な操作。
• 最適な実行プランを確保するようにインデックスを設計できるユーザー テーブルに関連する複雑な操作。インデックス スキャンまたは (理想的には) インデックス シークを優先して、テーブルのスキャンと参照を回避できます。

コンカレンシーに関するその他の考慮事項:

• MERGE によって一意のキーの挿入と更新の両方が行われることが予期される一部のシナリオでは、HOLDLOCK を指定することで一意のキー違反が回避されます。 HOLDLOCK は SERIALIZABLE トランザクション分離レベルのシノニムです。このトランザクションで読み取られたデータを他の同時実行トランザクションで変更することはできません。 SERIALIZABLE は最も安全な分離レベルですが、読み取り中にファントム行が挿入または更新されるのを防ぐために、データ範囲のロックを保持する他のトランザクションとの最小のコンカレンシーを提供します。 HOLDLOCK の詳細については、「ヒント」と「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」を参照してください。

JOIN に関するベスト プラクティス

MERGE ステートメントのパフォーマンスを向上させて正しい結果が得られるようにするには、次の結合のガイドラインに従うことをお勧めします。

• <merge_search_condition> 句で、ソース テーブルとターゲット テーブルのデータを照合する基準を判断する検索条件のみを指定します。 つまり、対象テーブルのうち、ソース テーブル内の対応する列と比較する列のみを指定します。
• 定数などのその他の値との比較を含めないでください。

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

• 適切な WHEN 句で、行をフィルター選択するための検索条件を指定します。 たとえば、WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT.... のように指定します。
• フィルター選択された行を返すソースまたは対象のビューを定義して、そのビューをソース テーブルまたは対象テーブルとして参照します。 ビューが対象テーブルに対して定義されている場合、これに対するアクションはビューの更新条件を満たす必要があります。 ビューを使用してデータを更新する方法の詳細については、「ビューを使用したデータ変更」を参照してください。
• WITH <common table expression> 句を使用して、ソース テーブルまたは対象テーブルから行を除外します。 この方法は、ON 句で追加の検索条件を指定する方法に似ており、不適切な結果が返される可能性があります。 この方法を使用しないようにするか、十分にテストしてから実装することをお勧めします。

MERGE ステートメントでの結合操作は、SELECT ステートメントでの結合と同じ方法で最適化されます。 つまり、SQL Server で結合を処理する場合、クエリ オプティマイザーにより、複数の候補の中から最も効率的な結合の処理方法が選択されます。 ソースと対象が同じようなサイズで、前に説明したインデックスのガイドラインがソース テーブルと対象テーブルに適用されている場合は、Merge Join 操作が最も効率的なクエリ プランになります。 これは、両方のテーブルが 1 回だけスキャンされ、データを並べ替える必要がないためです。 ソース テーブルが対象テーブルよりも小さい場合は、Nested Loops 操作をお勧めします。

MERGE ステートメントで OPTION (<query_hint>) 句を指定することで、特定の結合を強制的に使用することができます。 MERGE ステートメントのクエリ ヒントとしてハッシュ結合を使用しないことをお勧めします。この種類の結合ではインデックスが使用されないためです。

パラメーター化に関するベスト プラクティス

パラメーターを指定せずに SELECT、INSERT、UPDATE、または DELETE ステートメントを実行した場合、SQL Server のクエリ オプティマイザーの内部でステートメントがパラメーター化される可能性があります。 これは、クエリに含まれるリテラル値がすべてパラメーターに置き換えられることを意味します。 たとえば、ステートメント INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) は内部で INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) として実装される可能性があります。 簡易パラメーター化と呼ばれるこの処理によって、新しい SQL ステートメントと既存のコンパイル済みの実行プランとを照合するリレーショナル エンジンの機能が向上します。 クエリをコンパイルおよび再コンパイルする頻度が下がるので、クエリのパフォーマンスが向上する可能性があります。 クエリ オプティマイザーでは、簡易パラメーター化処理は MERGE ステートメントには適用されません。 したがって、リテラル値を含む MERGE ステートメントと、個々の INSERT、UPDATE、または DELETE ステートメントは、MERGE ステートメントが実行されるたびに新しいプランがコンパイルされるため、実行されない可能性があります。

クエリのパフォーマンスを向上させるには、次のパラメーター化のガイドラインに従うことをお勧めします。

• MERGE ステートメントの ON <merge_search_condition> 句と WHEN 句ですべてのリテラル値をパラメーター化します。 たとえば、リテラル値を適切な入力パラメーターに置き換えるストアド プロシージャに MERGE ステートメントを組み込むことができます。
• ステートメントをパラメーター化できない場合は、TEMPLATE 型のプラン ガイドを作成し、そのプラン ガイドで PARAMETERIZATION FORCED クエリ ヒントを指定します。 詳細については、「プラン ガイドを使用したクエリのパラメーター化動作の指定」を参照してください。
• MERGE ステートメントがデータベースで頻繁に実行される場合は、データベースの PARAMETERIZATION オプションを FORCED に設定することを検討します。 このオプションを設定する場合は注意が必要です。 PARAMETERIZATION オプションはデータベース レベルの設定で、データベースに対するすべてのクエリの処理方法に影響します。 詳細については、「強制パラメーター化」を参照してください。
• プラン ガイドより新しい簡単な代替手段として、クエリ ストア ヒントを使用する同様の方針を検討します。 詳細については、「クエリ ストアのヒント」を参照してください。

TOP 句に関するベスト プラクティス

MERGE ステートメントの TOP 句では、ソース テーブルと対象テーブルが結合され、挿入、更新、または削除操作の対象とならない行が削除された後に影響を受ける行の数または割合を指定します。 TOP 句を使用すると、結合された行の数が指定の値まで減少し、挿入、更新、または削除操作が残りの結合された行に順序付けなしで適用されます。 つまり、WHEN 句に定義された操作に行が割り当てられる順序は決まっていません。 たとえば、TOP (10) と指定すると 10 行に影響しますが、そのうち 7 行が更新されて 3 行が挿入される場合も、1 行が削除、5 行が更新され、4 行が挿入される場合もあります。

一般に、TOP 句は、データ操作言語 (DML) 操作を大きなテーブルに対してバッチで実行するために使用します。 このために MERGE ステートメントで TOP 句を使用する場合は、次の影響について理解しておくことが重要です。

• I/O のパフォーマンスに影響する可能性があります。

  MERGE ステートメントでは、ソース テーブルと対象テーブルの両方のフル テーブル スキャンが実行されます。 操作をバッチに分割すると、バッチごとに実行される書き込み操作の数は減少しますが、各バッチでソース テーブルと対象テーブルのフル テーブル スキャンが実行されます。 結果として得られる読み取りアクティビティは、テーブルに対するクエリおよび他の同時実行アクティビティのパフォーマンスに影響を与える可能性があります。

• 不適切な結果になる可能性があります。

  一連のすべてのバッチが新しい行を対象としていることを確認することが重要です。そうしないと、対象テーブルに重複行が誤って挿入されるなどの不適切な動作が発生するおそれがあります。 このような動作は、対象バッチには存在しないが対象テーブル全体には存在する行がソース テーブルに含まれている場合に発生することがあります。 正しい結果を得るには、次のようにします。

  • ON 句を使用して、既存の対象行に影響するソース行と本当に新しい行を特定します。
  • WHEN MATCHED 句で追加条件を使用して、対象行が以前のバッチで既に更新されたかどうかを調べます。
  • WHEN MATCHED 句と SET ロジックで追加条件を使用して、同じ行を 2 回更新できないことを確認します。

TOP 句はこれらの句が適用された後にのみ適用されるので、実行ごとに本当に一致しない 1 行が挿入されるか、既存の 1 行が更新されます。

一括読み込みに関するベスト プラクティス

MERGE ステートメントを使用すると、OPENROWSET(BULK...) 句をテーブル ソースとして指定することによって、ソース データ ファイルから対象テーブルにデータを効率よく一括読み込みできます。 これにより、ファイル全体が単一のバッチで処理されます。

一括マージ処理のパフォーマンスを向上させるには、次のガイドラインに従うことをお勧めします。

• 対象テーブルの結合列にクラスター化インデックスを作成します。

• 一括読み込み MERGE の間、ターゲット テーブルで他の一意でない非クラスター化インデックスを無効にし、後で有効にします。 これは、夜間の一括データ操作では一般的で便利な方法です。

• OPENROWSET(BULK...) 句で ORDER ヒントと UNIQUE ヒントを使用して、ソース データ ファイルの並べ替え方法を指定します。

  既定では、一括操作はデータ ファイルが並べ替えられていないことを前提に実行されます。 そのため、ソース データが対象テーブルのクラスター化インデックスに従って並べ替えられることと、クエリ オプティマイザーでより効率的なクエリ プランを生成できるように ORDER ヒントを使用して順序を指定することが重要です。 ヒントは実行時に検証されます。データ ストリームが指定されたヒントに適合していない場合は、エラーが発生します。

これらのガイドラインによって結合キーが一意になり、ソース ファイルのデータの並べ替え順が対象テーブルと一致するようになります。 追加の並べ替え操作が不要になり、意味のないデータのコピーが必要なくなるので、クエリのパフォーマンスが向上します。

MERGE パフォーマンスの測定と診断

次の機能を使用すると、MERGE ステートメントのパフォーマンスの測定と診断に役立ちます。

• sys.dm_exec_query_optimizer_info 動的管理ビューの merge stmt カウンターを使用すると、MERGE ステートメントに対するクエリの最適化の数が返されます。
• sys.dm_exec_plan_attributes 動的管理ビューの merge_action_type 属性を使用すると、MERGE ステートメントの結果として使用されるトリガーの実行プランの種類が返されます。
• 拡張イベント セッションを使用すると、MERGE ステートメントのトラブルシューティング データが、その他のデータ操作言語 (DML) ステートメントの場合と同じ方法で収集されます。 拡張イベントの概要について詳しくは、「クイック スタート: 拡張イベント」および 「SSMS XEvent Profiler の使用」に関するページをご覧ください。

例

A. MERGE を使用して、単一のステートメントでテーブルに INSERT 操作と UPDATE 操作を実行する

一般的なシナリオは、一致する行が存在する場合、テーブル内の 1 つまたは複数の列を更新することです。 または、一致する行が存在しない場合は、データを新しい行として挿入します。 通常は、適切な UPDATE ステートメントと INSERT ステートメントを含むストアド プロシージャにパラメーターを渡すことで、どちらかを実行します。 MERGE ステートメントを使用すると、単一のステートメントで両方のタスクを実行できます。 次に示すのは、INSERT ステートメントと UPDATE ステートメントの両方を含む AdventureWorks2022 データベースのストアド プロシージャの例です。 このプロシージャを、単一の MERGE ステートメントで同等の操作を実行するように変更します。

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

B. MERGE を使用して、単一のステートメントでテーブルに UPDATE 操作と DELETE 操作を実行する

次の例では、MERGE を使って、ProductInventory テーブルで処理される注文に基づき、AdventureWorks2022 サンプル データベースの SalesOrderDetail テーブルを毎日更新します。 Quantity テーブルで各製品のその日の注文数を差し引くことで、ProductInventory テーブルの SalesOrderDetail 列を更新します。 製品の注文数によって、製品の在庫レベルが 0 以下に低下した場合は、その製品の行が ProductInventory テーブルから削除されます。

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C: MERGE で、派生ソース テーブルを使用して対象テーブルに UPDATE 操作と INSERT 操作を実行する

次の例では、MERGE を使って、行を更新または挿入することで AdventureWorks2022 データベースの SalesReason テーブルを変更します。

ソース テーブルの NewName の値が対象テーブル (SalesReason) の Name 列の値と一致すると、対象テーブルの ReasonType 列が更新されます。 NewName の値が一致しない場合は、ソース行が対象テーブルに挿入されます。 ソース テーブルは、Transact-SQL テーブル値コンストラクターを使用して、ソース テーブルに対して複数の行を指定する派生テーブルです。 派生テーブルでテーブル値コンストラクターを使用する方法の詳細については、「テーブル値コンストラクター (Transact-SQL)」をご覧ください。

OUTPUT 句は、MERGE ステートメントの結果を照会するのに便利な場合があります。詳細については、「OUTPUT 句」を参照してください。 この例では、テーブル変数内の OUTPUT 句の結果を格納する方法も示します。 さらに、挿入された行と更新された行の数を返す単純な選択操作を実行して、MERGE ステートメントの結果を集計します。

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. MERGE ステートメントの結果を別のテーブルに挿入する

次の例では、MERGE ステートメントの OUTPUT 句から返されたデータをキャプチャし、そのデータを別のテーブルに挿入します。 この MERGE ステートメントは、SalesOrderDetail テーブルで処理される注文に基づいて、AdventureWorks2022 データベースの ProductInventory テーブルの Quantity 列を更新します。 この例では、更新された行をキャプチャし、在庫変更の追跡に使用する別のテーブルに挿入します。

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

E. MERGE を使用して、グラフ データベース内のターゲット エッジ テーブルに対する INSERT または UPDATE を実行する

この例では、ノード テーブル Person および City と、エッジ テーブル livesIn を作成します。 livesIn エッジに対して MERGE ステートメントを使用し、Person と City の間にエッジがまだ存在していなければ、新しい行を挿入します。 エッジが既に存在する場合は、livesIn エッジに対して StreetAddress 属性の更新を行います。

-- CREATE node and edge tables
CREATE TABLE Person
(
    ID INTEGER PRIMARY KEY,
    PersonName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY,
    CityName VARCHAR(100),
    StateName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE livesIn
(
    StreetAddress VARCHAR(100)
)
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

関連するコンテンツ

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• OUTPUT 句 (Transact-SQL)
• Integration Services パッケージで MERGE を実行する
• FROM (Transact-SQL)
• テーブル値コンストラクター (Transact-SQL)