MERGEステートメントの概要とSQLServerデータの変更
MERGEステートメントは、葯から一致した値に基づいて1つのテーブルを変更するために使用されます。挿入、更新、および削除操作を1つのステートメントに組み合わせるために使用できます。この記事では、MERGEステートメントの使用方法について説明します。いくつかのベストプラクティス、制限、およびまとめについて、いくつかの例を挙げて説明します。
これは、一連の記事の5番目の記事です。最初に、SQLServerデータ変更ステートメントの概要を読むことから始めることができます。
このレッスンのすべての例は、Microsoft SQL Server ManagementStudioとAdventureWorks2012データベースに基づいています。これらの無料ツールの使用を開始するには、ガイドを使用してSQLServerの使用を開始します
始める前に
この記事では例としてAdventureWorksデータベースを使用していますが、作成することにしました。対象となる概念をわかりやすく説明するためにデータベース内で使用するいくつかのサンプルテーブル。実行する必要のあるスクリプトはここにあります。 MERGEに関連する特別なセクションがあることに注意してください。
基本構造
MERGEステートメントは、INSERT、DELETE、およびUPDATE操作を1つのテーブルに結合します。仕組みを理解すると、3つのステートメントすべてを個別に使用してデータを同期することで、手順が簡素化されることがわかります。
以下は、マージステートメントの一般化された形式です。
MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement
マージステートメントは、sourceTableとtargetTableの2つのテーブルを使用して機能します。 targetTableは、sourceTable内に含まれるデータに基づいて変更されるテーブルです。
2つのテーブルはmergeConditionを使用して比較されます。この条件は、sourceTableの行をtargetTableに一致させる方法を指定します。 INNER JOINSに精通している場合は、これを行の照合に使用される結合条件と考えることができます。
通常、主キーなどの一意の識別子と照合します。ソーステーブルがNewProductであり、ターゲットProductMasterであり、両方のProductIDの主キーである場合、使用する適切なマージ条件は次のようになります。
NewProduct.ProductID = ProductMaster.ProductID
マージ条件の結果3つの状態のいずれか：MATCHED、NOT MATCHED、またはNOT MATCHED BYSOURCE。
マージ条件
さまざまな条件の意味を確認しましょう：
MATCHED –これらは一致条件を満たす行です。これらは、ソーステーブルとターゲットテーブルの両方に共通です。この図では、それらは緑色で示されています。合併ステートメントでこの条件を使用すると、 sourceTable列の値でターゲット行の列を更新するのとほとんど同じです。
NOT MATCHED –これはNOT MATCHED BYTARGETとも呼ばれます。これらは、ターゲットテーブルのどの行とも一致しなかったソーステーブルの行です。これらの行は、上の青い領域で表されます。ほとんどの場合、ソース行をtargetTableに追加する必要があることを推測するために使用できます。
ソースと一致しません–これらは、ソースレコードと一致しなかったターゲットテーブルの行です。これらはオレンジ色の領域の行です。 targetTableデータをソースと完全に同期することが目的の場合は、この一致条件を使用して行を削除します。
これがどのように機能するかを理解できない場合は、マージ条件が次のようになっていると考えてください。結合条件。緑のセクションのROWSは、マージ条件に一致する行を表します。青のセクションの行は、SourceTableにあるが、ターゲットにはない行です。オレンジ色のセクションの行は、ターゲットでのみ見つかった行です。
これらの一致するシナリオを指定すると、追加、削除、更新のアクティビティを1つのステートメントに簡単に組み込んで、2つの間の変更を同期できます。テーブル。
例を見てみましょう。
MERGEの例
esqlProductSourceに加えられた変更をesqlProductTargetと同期することが目標であると仮定しましょう。これら2つのテーブルの図を次に示します。
注：この例のために、説明したスクリプトを実行しました。 esqlProductSourceとesqlProductTargetの2つのテーブルを作成してデータを設定するための概要で。
MERGEステートメントを作成する前に、UPDATE、INSERT、およびDELETEステートメントを使用してテーブルを同期して変更および追加する方法を見てみましょう。 、およびターゲットテーブルの行を削除します。
これを個別に行う方法を確認したら、1つの操作に組み合わせて確認する方が理にかなっていると思います。
UPDATEを使用して変更を同期するあるテーブルから次のテーブルへ
製品ソースで変更された値でターゲットテーブルを更新するには、UPDATEステートメントを使用できます。 ProductIDは両方のテーブルの主キーであるため、テーブル間の行に一致する最適な選択肢になります。
ソース列を使用してターゲットテーブルの列値を更新する場合は、次の更新ステートメントを使用して更新できます
UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID
このステートメントは、esqlProductTargetの列を、esqlProductSourceで見つかった対応する列値で更新してproductIDと一致させます。
1つのテーブルで見つかったが他のテーブルでは見つからなかったINSERT行
次に、どのように処理するかを見てみましょう。製品ターゲットに挿入する必要があるソーステーブルの行を識別できます。これを行うには、サブクエリを使用して、ターゲットにないソーステーブルの行を検索できます。
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
注：外部結合を使用することもできます。同じことをします。理由に興味がある場合は、この記事を確認してください。
このステートメントは、esqlProductTargetにないesqlProductSourceのすべての行からesqlProductTargetに新しい行を挿入します。
削除行
最後に実行する必要のある同期アクティビティでは、SQLソースにないターゲットテーブルの行がすべて削除されます。挿入ステートメントで行ったように、サブクエリを使用します。ただし、今回は、esqlProductSourceに見つからないesqlProductTargetの行を識別します。使用できるDELETEステートメントは次のとおりです。
DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)
さまざまな操作を個別に実行する方法を確認したので、それらがどのように組み合わされるかを見てみましょう。マージステートメント。
大部分の手間のかかる作業は、マージ条件とその結果によって行われることに注意してください。削除ステートメントをintしたように、一致を繰り返し設定する必要はなく、1回実行されます。
Insertステートメントを上記のmergeステートメントと再度比較します。
INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)
MERGEステートメントがソーステーブルとターゲットテーブル、およびそれらがどのように一致するかを確立すると、赤で色分けされたものはすべて冗長になります。したがって、マージの挿入部分にはありません。
OUTPUTを使用したMERGE変更のログ記録
OUTPUT句を使用して変更をログに記録できます。この場合、特別な変数$ actionを使用して、マージアクションをログに記録できます。この変数は、「INSERT」、「UPDATE」、「DELETE」の3つの値のいずれかを取ります。
引き続き例を使用しますが、今回は変更をログに記録し、変更。
上記を新しいサンプルデータで実行すると、次の要約が生成されます。
