SQL SERVER - MERGE でテーブルのデータを同期する

MERGE 文を使ってテーブルのデータを同期する

同じような構造の二つのテーブルがあって、そのデータを同期したいような時ありませんか？

具体的には次のような処理をしたい時です。　

変更元となるテーブルをソーステーブル、変更先のテーブルをターゲットテーブルとして、

ソーステーブルのデータがターゲットテーブルに存在する時は UPDATE
ソーステーブルのデータがターゲットテーブルに存在しない時は INSERT
ターゲットテーブルにしか存在しない時は DELETE

これらの処理を行うことによって、ターゲットテーブルのデータはソーステーブルのデータと同じなるはずです。

MERGE 文を使うと、この UPDATE、INSERT、DELETE の処理を一度に行うことができます。

今回は簡単な MERGE 文を使ってテーブルのデータを同期する方法をご紹介します。

MERGE 文で同時に挿入・更新・削除を行う

今回使う、シンプルな MERGE ステートメントの構文は次の通りです。

MERGE <target_table>
USING <table_source>
ON <merge_search_condition>  
WHEN MATCHED 
　　　THEN <merge_matched>
WHEN NOT MATCHED BY TARGET
　　　THEN <merge_not_matched_by_target>
WHEN NOT MATCHED BY SOURCE
　　　THEN <merge_not_matched_by_source>;

<target_table>: ターゲット（変更先）のテーブル
<table_source>: ソース（変更元）のテーブル
<merge_search_condition>: マージの結合条件
<merge_matched>: ソーステーブルのデータがターゲットテーブルに存在する時に実行される句
<merge_not_matched_by_target>: ソーステーブルのデータがターゲットテーブルに存在しない時に実行される句
<merge_not_matched_by_source>: ターゲットテーブルにしか存在しない時に実行される句

他にもオプションや条件の追加、結果の出力などいろいろありますので、興味のある方はマイクロソフトの MERGE (Transact-SQL) のページでご確認ください。

では、上のマージ文を使って、違うデータベースにある Student テーブルのデータを同期してみましょう。

まずデータの準備をします。 Student テーブルが存在しない Test4 と Test5 というデータベースがある前提で、以下のスクリプトを実行して Student テーブルを作ります。

CREATE TABLE Test4.dbo.Student (
   StudentID INT NOT NULL PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

INSERT INTO Test4.dbo.Student
  (StudentID, FirstName, LastName, Birthday, Gender)
 VALUES
  (1, 'Taro', 'Yamada', '1980-02-15', 'M'),
  (2, 'Hanako', 'Tanaka' ,'1979-12-30', 'F'),
  (3, 'Yuko', 'Suzuki', '1979-07-07', 'F'),
  (4, 'Takao', 'Sato', '1980-03-12', 'M'),
  (5, 'Hiroki', 'Takagi', '1979-04-05', 'M'),
  (6, 'Yuka', 'Kimura', '1981-03-27', 'F');
-----------------------------------------
CREATE TABLE Test5.dbo.Student (
   StudentID INT NOT NULL PRIMARY KEY,
   FirstName VARCHAR(50) NULL,
   LastName VARCHAR(50) NULL,
   Birthday DATE NULL,
   Gender CHAR(1) NULL
);

INSERT INTO Test5.dbo.Student
  (StudentID, FirstName, LastName, Birthday, Gender)
 VALUES
  (1, 'Taro', 'Yamamoto', '1980-02-15', 'M'),
  (2, 'Hanako', 'Tanaka' ,'1979-12-30', 'F'),
  (4, 'Takao', 'Sato', '1980-03-12', 'M'),
  (5, 'Hiroki', 'Takagi', '1979-05-04', 'M'),
  (7, 'Sakura', 'Aoyama', '1995-02-20', 'F');

Test4 と Test5 の Student テーブルのレコードは次のようになります。

SELECT * FROM Test4.dbo.Student;
SELECT * FROM Test5.dbo.Student;

MERGE を使って、Test4 データベースの Student テーブルのレコードが Test5 データベースの Student テーブルのレコードと同じになるように同期してみましょう。

この場合、Test4.dbo.Student がターゲットテーブル、Test5.dbo.Student がソーステーブルになります。

ターゲットテーブルとソーステーブルの結合条件は StudentID として、二つのテーブルを同期するクエリーは次の通りです。

MERGE Test4.dbo.Student AS T
USING Test5.dbo.Student AS S 
ON T.StudentID = S.StudentID
WHEN MATCHED
   THEN UPDATE 
      SET
         T.FirstName = S.FirstName,
         T.LastName = S.LastName,
         T.Birthday = S.Birthday,
         T.Gender = S.Gender
WHEN NOT MATCHED BY TARGET 
   THEN INSERT (StudentID, FirstName, LastName, Birthday, Gender)
            VALUES (S.StudentID, S.FirstName, S.LastName, S.Birthday, S.Gender)
WHEN NOT MATCHED BY SOURCE 
   THEN DELETE;

同じ StudentID を持つレコードが存在する時は WHEN MATCHED の UPDATE 句が実行されます。（緑枠）

ソーステーブルと同じ StudentID を持つレコードがターゲットテーブルに存在しない場合は WHEN NOT MATCHED BY TARGET の INSERT 句が実行されます。（黄枠）

ターゲットテーブルに存在しない場合 StudentID を持つレコードがある場合は WHEN NOT MATCHED BY SOURCE の DELETE 句が実行され、ターゲットテーブルのレコードが削除されます。（赤線）

上記の MERGE ステートメントを実行した後に、Test4 と Test5 データベースの Student テーブルのレコードを確認するとデータが同期されていますね。

SELECT * FROM Test4.dbo.Student;
SELECT * FROM Test5.dbo.Student;

以上、MERGE 文を使って二つのテーブルのデータを同期する方法をご紹介しました。