MERGE（Transact-SQL）

• 2019年8月20日
• 読むのに25分
• • X
  • c
  • j
  • r
  • a
  • +18

適用対象： SQL Server（サポートされているすべてのバージョン） AzureSQLデータベース Azure Synapse Analytics

からターゲットテーブルに対して挿入、更新、または削除操作を実行します。ソーステーブルとの結合の結果。たとえば、一方のテーブルで見つかった違いに基づいて、一方のテーブルの行を挿入、更新、または削除して2つのテーブルを同期します。

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

Transact-SQL構文規則

構文

引数

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

TOP（式）
影響を受ける行の数またはパーセンテージを指定します。式は、行の数またはパーセンテージのいずれかです。 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
< table_source は、< clause_search_condition >に基づいて照合されます。target_tableは、挿入、更新、または削除操作のターゲットです。 MERGEステートメントのWHEN句で指定されます。

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

target_tableをリモートテーブルにすることはできません。 target_tableにルールを定義することはできません。

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

USING < table_source >
< merge_search条件に基づいて、target_tableのデータ行と一致するデータソースを指定します>。この一致の結果は、MERGEステートメントのWHEN句によって実行されるアクションを示します。 < table_source >は、リモートテーブルまたはリモートテーブルにアクセスする派生テーブルにすることができます。

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

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

この句の構文と引数の詳細については、FROM（Transact-SQL）を参照してください。

ON < merge_search_condition >
< table_source >はtarget_tableと結合して、一致する場所を判別します。

一致した場合< merge_matched >
< table_source > ON < merge_search_condition >であり、追加の検索条件を満たす場合は、< merge_matched 句。

MERGEステートメントには、最大2つのWHENMATCHED句を含めることができます。 2つの句を指定する場合、最初の句にはAND < search_condition >句を付ける必要があります。任意の行について、2番目のWHENMATCHED句は最初の句が適用されない場合にのみ適用されます。2つのWHENMATCHED句がある場合、1つはUPDATEアクションを指定し、もう1つはDELETEアクションを指定する必要があります。UPDATEが

merge_matched >句、および複数行の< table_source >は< merge_search_condition >に基づいてtarget_tableの行と一致し、SQLServerはエラーを返します。MERGEステートメントは” t同じ行を複数回更新するか、同じ行を更新して削除します。

一致しない場合< merge_not_matched >
< table_source > ON merge_search_condition >がtargの行と一致しませんet_tableですが、存在する場合は追加の検索条件を満たす。挿入する値は、< merge_not_matched >句で指定されます。 MERGEステートメントには、WHEN NOTMATCHED句を1つだけ含めることができます。

WHEN NOT MATCHED BY SOURCE THEN < merge_matched >
* target_tableのすべての行が、< table_source > ON < merge_search_condition >で、追加の検索条件を満たすものは、< merge_matchedに従って更新または削除されます。 >句。

MERGEステートメントには、最大2つのWHEN NOT MATCHED BY SOURCE句を含めることができます。2つの句を指定する場合は、最初の句に付随する必要があります。 AND < clause_search_condition >句。任意の行に対して、2番目のWHEN NOT MATCHED BYSOURCE句は最初の句が適用されない場合にのみ適用されます。 t。 WHEN NOT MATCHED BY SOURCE句が2つある場合、1つはUPDATEアクションを指定し、もう1つは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 >
各thのターゲットテーブルに適用する1つ以上のテーブルヒントを指定しますe MERGEステートメントによって実行されたアクションを挿入、更新、または削除します。 WITHキーワードと括弧は必須です。

NOLOCKとREADUNCOMMITTEDは許可されていません。テーブルヒントの詳細については、「テーブルヒント（Transact-SQL）」を参照してください。

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

INDEX（index_val）
ソーステーブルとの暗黙的な結合を行うためのターゲットテーブル上の1つ以上のインデックスの名前またはIDを指定します。詳細については、「テーブルのヒント（トランザクション」」を参照してください。 -SQL）。

< output_clause >
target_tableのすべての行の行を返します。更新、挿入、または削除。順不同。 $ actionは、output句で指定できます。 $ actionは、nvarchar（10）型の列であり、各行に対して実行されたアクションに応じて、「INSERT」、「UPDATE」、または「DELETE」の3つの値のいずれかを返します。この句の引数と動作の詳細については、OUTPUT句（Transact-SQL）を参照してください。

< merge_matched >
< table_source ON < merge_search_condition >であり、追加の検索条件を満たす。

UPDATE SET < set_clause >
ターゲットテーブルで更新する列名または変数名のリストと、それらを更新するための値を指定します。

この句の引数の詳細については、UPDATE（Transact-SQL）を参照してください。変数を列と同じ値に設定することはサポートされていません。

DELETE
target_tableの行に一致する行を削除することを指定します。

< merge_not_matched >
ターゲットテーブルに挿入する値を指定します。

（column_list）
1つまたはデータを挿入するターゲットテーブルの列がさらにあります。列は単一パーツ名として指定する必要があります。そうしないと、MERGEステートメントが失敗します。column_listは括弧で囲み、コンマで区切る必要があります。

VALUES（ values_list）
ターゲットテーブルに挿入する値を返す定数、変数、または式のコンマ区切りのリスト。式にEXECUTEステートメントを含めることはできません。

DEFAULT VALUES
強制的に各列に定義されたデフォルト値を含むように行を挿入しました。

この句の詳細については、INSERT（Transact-SQL）を参照してください。

< search_condition >
Speci検索条件を指定して、< merge_search_condition >または< clause_search_condition 。この句の引数の詳細については、検索条件（Transact-SQL）を参照してください。

<グラフ検索パターン>
グラフの一致パターンを指定します。この句の引数の詳細については、MATCH（Transact-SQL）を参照してください

備考

Azure SynapseAnalyticsのMERGECLAUSE	サポートされているTARGE配布テーブル	サポートされているSOURCE配布テーブル	コメント
一致した場合	すべての配布タイプ	すべての配布タイプ
ターゲットと一致しません	HASH	すべての配布タイプ	UPDATE / DELETEFROM…JOINを使用して2つのテーブルを同期します。
ソースと一致しません	すべての配布タイプ	すべての配布タイプ

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

MERGEステートメントによってターゲットテーブルに指定された挿入、更新、または削除アクションは、カスケード参照整合性制約を含む、ターゲットテーブルに定義された制約によって制限されます。ターゲットテーブルの一意のインデックスに対してIGNORE_DUP_KEYがONの場合、MERGEはこの設定を無視します。

MERGEステートメントには、ステートメントターミネータとしてセミコロン（;）が必要です。ターミネータなしでMERGEステートメントを実行すると、エラー10713が発生します。

MERGEの後に使用すると、@@ ROWCOUNT（Transact-SQL）は、クライアントに挿入、更新、および削除された行の総数を返します。

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

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

トリガーの実装

で指定されたすべての挿入、更新、または削除アクションに対してMERGEステートメントの場合、SQL Serverは、ターゲットテーブルで定義された対応するAFTERトリガーを起動しますが、トリガーを最初または最後に起動するアクションを保証するものではありません。同じアクションに対して定義されたトリガーは、指定した順序を尊重します。トリガーの起動順序の設定の詳細については、「最初と最後のトリガーの指定」を参照してください。

ターゲットテーブルに、MERGEステートメントによって実行される挿入、更新、または削除アクションに対して有効なINSTEADOFトリガーが定義されている場合、MERGEステートメントで指定されたすべてのアクションに対して有効なINSTEADOFトリガーが必要です。

INSTEAD OFUPDATEまたはINSTEADOF DELETEトリガーがtarget_tableで定義されている場合、更新または削除操作は有効ではありません。代わりに、トリガーが起動し、挿入および削除されたテーブルがそれに応じて入力されます。

INSTEAD OF INSERTトリガーがtarget_tableで定義されている場合、挿入操作は実行されません。代わりに、テーブルはそれに応じて入力されます。

権限

ソーステーブルに対するSELECT権限と、ターゲットテーブルに対するINSERT、UPDATE、またはDELETE権限が必要です。詳細については、SELECT、INSERT、UPDATE、およびDELETEの記事の「権限」セクションを参照してください。

MERGEステートメントのパフォーマンスの最適化

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

インデックスのベストプラクティス

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

• ソーステーブルの結合列に一意でカバーするインデックスを作成します。
• ターゲットテーブルの結合列に一意のクラスター化インデックスを作成します。

これらのインデックスは、結合キーが一意であり、テーブル内のデータがソートされていることを保証します。クエリオプティマイザが重複行を見つけて更新するために追加の検証処理を実行する必要がなく、追加の並べ替え操作が必要ないため、クエリのパフォーマンスが向上します。

ベストプラクティスに参加

MERGEステートメントを実行し、正しい結果が得られるようにするには、次の結合ガイドラインをお勧めします。

• ON < merge_search_condition 句。つまり、ソーステーブルの対応する列と比較されるターゲットテーブルの列のみを指定します。
• 定数などの他の値との比較は含めないでください。

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

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

MERGEステートメントの結合操作は、SELECTステートメントの結合と同じ方法で最適化されます。つまり、SQL Serverが結合を処理するとき、クエリオプティマイザは、結合を処理するための最も効率的な方法（いくつかの可能性の中から）を選択します。ソースとターゲットのサイズが類似していて、前述のインデックスガイドラインがソーステーブルとターゲットテーブルに適用されている場合、マージ結合演算子が最も効率的なクエリプランです。これは、両方のテーブルが1回スキャンされ、データを並べ替える必要がないためです。ソースがターゲットテーブルよりも小さい場合は、ネストされたループ演算子を使用することをお勧めします。

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

パラメータ化のベストプラクティス

SELECT、INSERT、UPDATEの場合、またはDELETEステートメントがパラメーターなしで実行される場合、SQLServerクエリオプティマイザーはステートメントを内部的にパラメーター化することを選択できます。これは、クエリに含まれるリテラル値がパラメータに置き換えられることを意味します。たとえば、ステートメントINSERT dbo.MyTable（Col1、Col2）VALUES（1、10）は、INSERT dbo.MyTable（Col1、Col2）VALUES（@ p1、@ p2）として内部的に実装できます。単純なパラメーター化と呼ばれるこのプロセスにより、リレーショナルエンジンが、新しいSQLステートメントを既存の以前にコンパイルされた実行プランと照合する機能が向上します。クエリのコンパイルと再コンパイルの頻度が減るため、クエリのパフォーマンスが向上する可能性があります。クエリオプティマイザは、単純なパラメータ化プロセスをMERGEステートメントに適用しません。したがって、リテラル値を含むMERGEステートメントは、MERGEステートメントが実行されるたびに新しいプランがコンパイルされるため、個々のINSERT、UPDATE、またはDELETEステートメントと同様に機能しない可能性があります。

クエリのパフォーマンスを向上させるために、次のパラメータ化ガイドライン：

• ON <merge_search_condition>句とWHENのすべてのリテラル値をパラメータ化します。 MERGEステートメントの句。たとえば、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句で追加の条件を使用して、ターゲット行がすでに更新されているかどうかを判別します。前のバッチ。

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

一括読み込みのベストプラクティス

MERGEステートメントを使用すると、OPENROWSET(BULK…)句を指定することで、ソースデータファイルからターゲットテーブルにデータを効率的に一括読み込みできます。テーブルソースとして。そうすることで、ファイル全体が1つのバッチで処理されます。

一括マージプロセスのパフォーマンスを向上させるために、次のガイドラインをお勧めします。

• ターゲットテーブルの結合列にクラスタ化されたインデックスを作成します。

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

  デフォルトでは、一括操作ではデータファイルが順序付けされていないと想定されます。したがって、クエリオプティマイザがより効率的なクエリプランを生成できるように、ソースデータをターゲットテーブルのクラスタ化インデックスに従ってソートし、ORDERヒントを使用して順序を示すことが重要です。ヒントは実行時に検証されます。データストリームが指定されたヒントに準拠していない場合、エラーが発生します。

これらのガイドラインにより、結合キーが一意であり、データの並べ替え順序がソースファイルがターゲットテーブルと一致します。追加の並べ替え操作や不要なデータコピーが不要なため、クエリのパフォーマンスが向上します。

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

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

• sys.dm_exec_query_optimizer_info動的管理ビューのマージstmtカウンターを使用して、MERGEステートメント用のクエリ最適化の数を返します。
• 使用sys.dm_exec_plan_attributes動的管理ビューのmerge_action_type属性は、MERGEステートメントの結果として使用されるトリガー実行プランのタイプを返します。
• SQLトレースを使用して、同じ方法でMERGEステートメントのトラブルシューティングデータを収集します。他のデータ操作言語（DML）ステートメントの場合と同じです。詳細については、SQLトレースを参照してください。

例

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

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

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

次の例では、MERGEを使用してAdventureWorks2012サンプルデータベースのProductInventoryテーブルを更新します。 SalesOrderDetailテーブルで処理される注文に基づいて毎日。ProductInventoryのQuantity列div>テーブルは、 テーブル。商品の注文数によって商品の在庫レベルが0以下になると、その商品の行がProductInventoryテーブルから削除されます。

C。 MERGEを使用して、派生ソーステーブルを使用してターゲットテーブルでUPDATEおよびINSERT操作を実行する

次の例では、MERGEを使用してAdventureWorks2012データベースのSalesReasonテーブルを変更します。行を更新または挿入する。ソーステーブルのNewNameの値が、ターゲットテーブルのName列の値と一致する場合（SalesReason）、ReasonType列がターゲットテーブルで更新されます。 NewNameの値が一致しない場合、ソース行がターゲットテーブルに挿入されます。ソーステーブルは、Transact-SQLテーブル値コンストラクターを使用して複数を指定する派生テーブルです。ソーステーブルの行。派生テーブルでのテーブル値コンストラクターの使用の詳細については、「テーブル値コンストラクター（Transact-SQL）」を参照してください。この例は、OUTPUT句の結果をテーブル変数に格納する方法も示しています。次に、挿入および更新された行の数を返す単純なselect操作を実行して、MERGEステートメントの結果を要約します。

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

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

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

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

関連項目