SQL ServerのMERGE文をマスター：更新、挿入、削除を一括管理

複数のSQL操作を個別に書くのって、面倒じゃないですか？

例えば、データベースの更新、挿入、削除を個別のクエリで管理するのは大変です。そんな時に便利なのがSQL ServerのMERGE文です。MERGE文を使えば、一つのクエリでこれらの操作を効率的に行うことができます。本記事では、具体的な例を通じて、MERGE文の使い方とその利点について詳しく解説します。これであなたのデータベース管理もスムーズに進むことでしょう。

MERGE文の具体例
まとめ

MERGE文の具体例

実例1: 顧客情報の管理

顧客情報を最新の状態に保つためには、既存のデータを更新し、新しいデータを追加する必要があります。MERGE文を使用することで、これらの操作を一つのクエリで効率的に実行できます。MERGE文がない場合、更新と挿入の操作を別々に行う必要があり、複数のクエリを実行するため、コードが煩雑になり、実行時間も増加します。

テーブル構造: 顧客

ColumnName	DataType	Description
顧客ID	INT	顧客ID
名前	VARCHAR(50)	顧客の名前
電話番号	VARCHAR(15)	電話番号
メール	VARCHAR(100)	メールアドレス
登録日	DATE	登録日

既存データ:

顧客ID	名前	電話番号	メール	登録日
1	山田	090-1234-5678	yamada@example.com	2024-01-01
2	佐藤	090-2345-6789	sato@example.com	2024-02-01

更新元データ:

顧客ID	名前	電話番号	メール	登録日
1	山田	090-1234-5678	yamada_new@example.com	2024-01-01
3	鈴木	090-3456-7890	suzuki@example.com	2024-06-01

SQLクエリ:

MERGE INTO 顧客 AS 既存
USING (SELECT 1 AS 顧客ID, '山田' AS 名前, '090-1234-5678' AS 電話番号, 'yamada_new@example.com' AS メール, '2024-01-01' AS 登録日
       UNION ALL
       SELECT 3 AS 顧客ID, '鈴木' AS 名前, '090-3456-7890' AS 電話番号, 'suzuki@example.com' AS メール, '2024-06-01' AS 登録日) AS 更新元
ON 既存.顧客ID = 更新元.顧客ID
WHEN MATCHED THEN
    UPDATE SET 
        既存.名前 = 更新元.名前,
        既存.電話番号 = 更新元.電話番号,
        既存.メール = 更新元.メール,
        既存.登録日 = 更新元.登録日
WHEN NOT MATCHED BY TARGET THEN
    INSERT (顧客ID, 名前, 電話番号, メール, 登録日)
    VALUES (更新元.顧客ID, 更新元.名前, 更新元.電話番号, 更新元.メール, 更新元.登録日);

MERGE INTO 顧客 AS 既存

USING (SELECT 1 AS 顧客ID, '山田' AS 名前, '090-1234-5678' AS 電話番号, 'yamada_new@example.com' AS メール, '2024-01-01' AS 登録日

UNION ALL

SELECT 3 AS 顧客ID, '鈴木' AS 名前, '090-3456-7890' AS 電話番号, 'suzuki@example.com' AS メール, '2024-06-01' AS 登録日) AS 更新元

ON 既存.顧客ID = 更新元.顧客ID

WHEN MATCHED THEN

UPDATE SET

既存.名前 = 更新元.名前,

既存.電話番号 = 更新元.電話番号,

既存.メール = 更新元.メール,

既存.登録日 = 更新元.登録日

WHEN NOT MATCHED BY TARGET THEN

INSERT (顧客ID, 名前, 電話番号, メール, 登録日)

VALUES (更新元.顧客ID, 更新元.名前, 更新元.電話番号, 更新元.メール, 更新元.登録日);

出力結果:

顧客ID	名前	電話番号	メール	登録日
1	山田	090-1234-5678	yamada_new@example.com	2024-01-01
2	佐藤	090-2345-6789	sato@example.com	2024-02-01
3	鈴木	090-3456-7890	suzuki@example.com	2024-06-01

有用性: MERGE文を使用することで、更新と挿入を一つのクエリで効率的に行うことができます。これにより、複数のクエリを実行する必要がなくなり、コードのシンプルさと実行速度が向上します。特に大量のデータを処理する際には、MERGE文を使うことでパフォーマンスの向上が期待できます。

実例2: 在庫情報の更新と不足商品の削除

在庫管理において、最新の在庫情報を維持し、不要なデータを削除することは非常に重要です。MERGE文を使用することで、在庫情報の更新と不足商品の削除を一つのクエリで実行できます。MERGE文がない場合、更新と削除の操作を別々に行う必要があり、コードが複雑になり、パフォーマンスが低下します。

テーブル構造: 在庫

ColumnName	DataType	Description
商品ID	INT	商品ID
商品名	VARCHAR(100)	商品名
在庫数	INT	在庫数
最終更新日	DATE	最終更新日

既存データ:

商品ID	商品名	在庫数	最終更新日
1	りんご	50	2024-05-01
2	バナナ	0	2024-05-01

更新元データ:

商品ID	商品名	在庫数	最終更新日
1	りんご	60	2024-06-01
3	オレンジ	40	2024-06-01

SQLクエリ:

MERGE INTO 在庫 AS 既存
USING (SELECT 1 AS 商品ID, 'りんご' AS 商品名, 60 AS 在庫数, '2024-06-01' AS 最終更新日
       UNION ALL
       SELECT 3 AS 商品ID, 'オレンジ' AS 商品名, 40 AS 在庫数, '2024-06-01' AS 最終更新日) AS 更新元
ON 既存.商品ID = 更新元.商品ID
WHEN MATCHED THEN
    UPDATE SET 
        既存.商品名 = 更新元.商品名,
        既存.在庫数 = 更新元.在庫数,
        既存.最終更新日 = 更新元.最終更新日
WHEN NOT MATCHED BY SOURCE AND 既存.在庫数 = 0 THEN
    DELETE;

MERGE INTO 在庫 AS 既存

USING (SELECT 1 AS 商品ID, 'りんご' AS 商品名, 60 AS 在庫数, '2024-06-01' AS 最終更新日

UNION ALL

SELECT 3 AS 商品ID, 'オレンジ' AS 商品名, 40 AS 在庫数, '2024-06-01' AS 最終更新日) AS 更新元

ON 既存.商品ID = 更新元.商品ID

WHEN MATCHED THEN

UPDATE SET

既存.商品名 = 更新元.商品名,

既存.在庫数 = 更新元.在庫数,

既存.最終更新日 = 更新元.最終更新日

WHEN NOT MATCHED BY SOURCE AND 既存.在庫数 = 0 THEN

DELETE;

出力結果:

商品ID	商品名	在庫数	最終更新日
1	りんご	60	2024-06-01
3	オレンジ	40	2024-06-01

有用性: MERGE文を使用することで、在庫情報の更新と不足商品の削除を一度に実行できます。これにより、複数のクエリを実行する必要がなくなり、コードが簡潔になり、実行速度が向上します。また、在庫データの整合性を確保することで、効率的な在庫管理が可能になります。

実例3: 従業員データの更新とログ記録

従業員情報を更新し、その変更内容をログに記録することは、データのトレーサビリティを確保する上で重要です。MERGE文を使用することで、これらの操作を一つのクエリで効率的に実行できます。MERGE文がない場合、更新とログ記録を別々に行う必要があり、コードが複雑になり、データの一貫性が保たれにくくなります。

テーブル構造:従業員

ColumnName	DataType	Description
従業員ID	INT	従業員ID
名前	VARCHAR(50)	従業員の名前
部署	VARCHAR(50)	部署名
給与	INT	給与
最終更新日	DATE	最終更新日

テーブル構造:従業員ログ

ColumnName	DataType	Description
操作	VARCHAR(10)	操作の種類 (INSERT, UPDATE, DELETE)
従業員ID	INT	従業員ID
更新前データ	VARCHAR(255)	更新前のデータ
更新後データ	VARCHAR(255)	更新後のデータ
操作日時	DATETIME	操作日時

既存データ:

従業員ID	名前	部署	給与	最終更新日
1	田中	営業部	5000	2024-01-01
2	佐藤	経理部	5500	2024-02-01

更新元データ:

従業員ID	名前	部署	給与	最終更新日
1	田中	営業部	6000	2024-06-01
3	鈴木	開発部	6500	2024-06-01

SQLクエリ:

MERGE INTO 従業員 AS 既存
USING (SELECT 1 AS 従業員ID, '田中' AS 名前, '営業部' AS 部署, 6000 AS 給与, '2024-06-01' AS 最終更新日
       UNION ALL
       SELECT 3 AS 従業員ID, '鈴木' AS 名前, '開発部' AS 部署, 6500 AS 給与, '2024-06-01' AS 最終更新日) AS 更新元
ON 既存.従業員ID = 更新元.従業員ID
WHEN MATCHED THEN
    UPDATE SET 
        既存.名前 = 更新元.名前,
        既存.部署 = 更新元.部署,
        既存.給与 = 更新元.給与,
        既存.最終更新日 = 更新元.最終更新日
    OUTPUT 
        'UPDATE' AS 操作, 
        既存.従業員ID, 
        CONCAT(既存.名前, ',', 既存.部署, ',', 既存.給与, ',', 既存.最終更新日) AS 更新前データ,
        CONCAT(更新元.名前, ',', 更新元.部署, ',', 更新元.給与, ',', 更新元.最終更新日) AS 更新後データ, 
        GETDATE() AS 操作日時
    INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (従業員ID, 名前, 部署, 給与, 最終更新日)
    VALUES (更新元.従業員ID, 更新元.名前, 更新元.部署, 更新元.給与, 更新元.最終更新日)
    OUTPUT 
        'INSERT' AS 操作, 
        更新元.従業員ID, 
        NULL AS 更新前データ,
        CONCAT(更新元.名前, ',', 更新元.部署, ',', 更新元.給与, ',', 更新元.最終更新日) AS 更新後データ, 
        GETDATE() AS 操作日時
    INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
    OUTPUT 
        'DELETE' AS 操作, 
        既存.従業員ID, 
        CONCAT(既存.名前, ',', 既存.部署, ',', 既存.給与, ',', 既存.最終更新日) AS 更新前データ,
        NULL AS 更新後データ, 
        GETDATE() AS 操作日時
    INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時);

MERGE INTO 従業員 AS 既存

USING (SELECT 1 AS 従業員ID, '田中' AS 名前, '営業部' AS 部署, 6000 AS 給与, '2024-06-01' AS 最終更新日

UNION ALL

SELECT 3 AS 従業員ID, '鈴木' AS 名前, '開発部' AS 部署, 6500 AS 給与, '2024-06-01' AS 最終更新日) AS 更新元

ON 既存.従業員ID = 更新元.従業員ID

WHEN MATCHED THEN

UPDATE SET

既存.名前 = 更新元.名前,

既存.部署 = 更新元.部署,

既存.給与 = 更新元.給与,

既存.最終更新日 = 更新元.最終更新日

OUTPUT

'UPDATE' AS 操作,

既存.従業員ID,

CONCAT(既存.名前, ',', 既存.部署, ',', 既存.給与, ',', 既存.最終更新日) AS 更新前データ,

CONCAT(更新元.名前, ',', 更新元.部署, ',', 更新元.給与, ',', 更新元.最終更新日) AS 更新後データ,

GETDATE() AS 操作日時

INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時)

WHEN NOT MATCHED BY TARGET THEN

INSERT (従業員ID, 名前, 部署, 給与, 最終更新日)

VALUES (更新元.従業員ID, 更新元.名前, 更新元.部署, 更新元.給与, 更新元.最終更新日)

OUTPUT

'INSERT' AS 操作,

更新元.従業員ID,

NULL AS 更新前データ,

CONCAT(更新元.名前, ',', 更新元.部署, ',', 更新元.給与, ',', 更新元.最終更新日) AS 更新後データ,

GETDATE() AS 操作日時

INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT

'DELETE' AS 操作,

既存.従業員ID,

CONCAT(既存.名前, ',', 既存.部署, ',', 既存.給与, ',', 既存.最終更新日) AS 更新前データ,

NULL AS 更新後データ,

GETDATE() AS 操作日時

INTO 従業員ログ(操作, 従業員ID, 更新前データ, 更新後データ, 操作日時);

出力結果（従業員テーブル）:

従業員ID	名前	部署	給与	最終更新日
1	田中	営業部	6000	2024-06-01
3	鈴木	開発部	6500	2024-06-01

出力結果（従業員ログテーブル）:

操作	従業員ID	更新前データ	更新後データ	操作日時
UPDATE	1	田中,営業部,5000,2024-01-01	田中,営業部,6000,2024-06-01	2024-06-01 12:00:00
DELETE	2	佐藤,経理部,5500,2024-02-01	NULL	2024-06-01 12:00:00
INSERT	3	NULL	鈴木,開発部,6500,2024-06-01	2024-06-01 12:00:00

有用性: MERGE文を使用することで、更新、挿入、削除の操作を一つのクエリで効率的に実行し、それらの操作をログに記録することができます。これにより、データの一貫性を保ちつつ、複数のクエリを実行する手間を省くことができ、コードの可読性とメンテナンス性が向上します。特に、変更履歴を残すことで、データ変更のトレーサビリティを確保できる点が重要です。

補足

OUTPUT句は、SQL Serverでデータ変更操作（INSERT、UPDATE、DELETE、およびMERGE）の結果を返すために使用されます。これにより、変更された行の情報をキャプチャし、それを他のテーブルに挿入したり、結果セットとして返したりすることができます。以下に、OUTPUT句の基本的な使い方と、具体例を示します。

OUTPUT [inserted | deleted | <列名>] INTO <ターゲットテーブル> (<列名リスト>)

1	OUTPUT [inserted \| deleted \| <列名>] INTO <ターゲットテーブル> (<列名リスト>)

OUTPUT句では、insertedおよびdeleted仮想テーブルを使用して、変更された行の新しい値および古い値にアクセスします。

inserted: 新しく挿入または更新された行の新しい値を保持します。
deleted: 削除または更新される前の行の古い値を保持します。

このOUTPUT句により、従業員テーブルに対する更新、挿入、削除の各操作の詳細なログが記録されます。これにより、後からのトレーサビリティや監査が容易になります。

まとめ

今回の記事では、SQL ServerのMERGE文を使用して従業員データを効率的に更新、挿入、削除する方法について解説しました。

MERGE文を使用することで、以下の利点が得られます：

効率的なデータ管理:
- 更新、挿入、削除の操作を一つのクエリで実行できるため、コードの簡潔性と可読性が向上します。
- 複数のクエリを実行する必要がなく、データ処理の効率が高まります。
データの整合性:
- 一度に複数の操作を実行することで、データの一貫性を保ちやすくなります。
- 特に大量のデータを扱う場合や頻繁にデータ更新が行われる場合に有効です。

今回の例では、従業員テーブルに対する更新、挿入、削除の操作を行い、それぞれの操作の詳細をログテーブルに記録する方法を示しました。MERGE文を使うことで、変更されたデータの前後の状態を簡潔に記録し、後からの解析やレポート作成が容易になります。

このように、MERGE文を使用することで、データベース管理の効率と信頼性を大幅に向上させることができます。SQL Serverを使用したデータ管理において、ぜひ活用してみてください。