SQL Server でのデータ整合性の維持：重複なしで INSERT INTO SELECT を実行する方法

2024-06-10

ここでは、SQL ServerでINSERT INTO SELECTクエリで重複を回避する3つの主要な方法について説明します。

NOT EXISTS句を使用して、挿入しようとしているレコードがターゲットテーブルに既に存在するかどうかを確認できます。以下のクエリはこの方法を示しています。

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
WHERE NOT EXISTS (
    SELECT 1
    FROM target_table
    WHERE col1 = source_table.col1
        AND col2 = source_table.col2
        ...
);

このクエリは、source_tableテーブルからtarget_tableテーブルにレコードを挿入しようとします。NOT EXISTSサブクエリは、target_tableテーブルにsource_tableテーブルから各レコードと一致するレコードが存在するかどうかを確認します。一致するレコードが見つかった場合は、そのレコードは挿入されません。

EXCEPT句を使用して、source_tableテーブルとtarget_tableテーブルの差集合を取得できます。差集合は、source_tableテーブルに存在し、target_tableテーブルには存在しないレコードのみを含むテーブルです。以下のクエリはこの方法を示しています。

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
EXCEPT
SELECT col1, col2, ...
FROM target_table;

このクエリは、source_tableテーブルからtarget_tableテーブルにレコードを挿入しようとします。EXCEPT句は、source_tableテーブルからtarget_tableテーブルのすべてのレコードを差し引いたレコードのみを返します。これらのレコードは、target_tableテーブルに挿入されます。

MERGE文を使用する

MERGE文は、INSERT、UPDATE、およびDELETE操作を単一のステートメントで実行できる強力なツールです。重複レコードを回避するためにMERGE文を使用するには、次のとおりに使用できます。

MERGE INTO target_table AS target
USING source_table AS source
ON (target.col1 = source.col1
    AND target.col2 = source.col2
    ... )
WHEN MATCHED THEN
    UPDATE SET target.col3 = source.col3, ...
WHEN NOT MATCHED THEN
    INSERT (col1, col2, col3, ...)
    VALUES (source.col1, source.col2, source.col3, ...);

このクエリは、source_tableテーブルからtarget_tableテーブルにレコードをマージしようとします。ON句は、target_tableテーブルとsource_tableテーブルのレコードを一致させます。WHEN MATCHED句は、一致するレコードが見つかった場合に実行する操作を指定します。この例では、一致するレコードのcol3列がsource_tableテーブルの値に更新されます。WHEN NOT MATCHED句は、一致するレコードが見つからない場合に実行する操作を指定します。この例では、新しいレコードがtarget_tableテーブルに挿入されます。

使用する方法は、特定の状況によって異なります。

NOT EXISTS`句は、単純でわかりやすい方法ですが、複雑なクエリの場合は非効率になる可能性があります。
**EXCEPT句**は、効率的ですが、source_tableテーブルとtarget_table`テーブルの構造が同じである必要があることに注意する必要があります。
MERGE`文は、最も汎用性が高く、複雑なマージ操作を処理できますが、習得するのが最も難しい方法です。

その他の考慮事項

ユニークインデックスを使用して、ターゲットテーブルの重複レコードを回避することもできます。
INSERT...SELECTステートメントを実行する前に、ターゲットテーブルの既存データをバックアップすることをお勧めします。

例

以下の例は、NOT EXISTS句を使用して、CustomersテーブルからOrdersテーブルにレコードを挿入する方法を示しています。

INSERT INTO Orders (CustomerID, OrderDate, ProductID, Quantity)
SELECT CustomerID, OrderDate, ProductID, Quantity
FROM Sales
WHERE

SQL Server で INSERT INTO SELECT クエリで重複を回避するサンプルコード

NOT EXISTS 句を使用する

-- target_table テーブルにすでに存在するレコードは挿入しない
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
WHERE NOT EXISTS (
    SELECT 1
    FROM target_table
    WHERE col1 = source_table.col1
        AND col2 = source_table.col2
        ...
);

EXCEPT 句を使用する

-- source_table テーブルに存在し、target_table テーブルには存在しないレコードのみを挿入する
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
EXCEPT
SELECT col1, col2, ...
FROM target_table;

-- source_table テーブルから target_table テーブルにレコードをマージし、重複を回避する
MERGE INTO target_table AS target
USING source_table AS source
ON (target.col1 = source.col1
    AND target.col2 = source.col2
    ... )
WHEN MATCHED THEN
    UPDATE SET target.col3 = source.col3, ...
WHEN NOT MATCHED THEN
    INSERT (col1, col2, col3, ...)
    VALUES (source.col1, source.col2, source.col3, ...);

注:

上記のコード例はほんの一例です。実際の状況に合わせてクエリを調整する必要があります。
重複レコードを処理する方法の詳細については、SQL Server のドキュメントを参照してください。

以下の追加リソースもご参考ください:

SQL Server で INSERT INTO SELECT クエリで重複を回避するその他の方法

ROW_NUMBER() 関数は、クエリ結果セット内の各行に番号を割り当てるために使用できます。この番号を使用して、各テーブルに挿入する行を制御できます。以下のクエリはこの方法を示しています。

-- source_table テーブルから target_table テーブルに最新レコードのみを挿入する
INSERT INTO target_table (col1, col2, ...)
SELECT s.col1, s.col2, ...
FROM source_table AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM target_table AS t
    WHERE t.col1 = s.col1
        AND t.col2 = s.col2
        AND t.ROW_NUMBER() > s.ROW_NUMBER()
);

CTE を使用する

CTE (Common Table Expression) を使用して、複雑なクエリをより小さな、よりわかりやすい部分に分割できます。CTE を使用して重複を回避するには、次のとおりに使用できます。

-- source_table テーブルから target_table テーブルに最新レコードのみを挿入する
WITH latest_records AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS row_num
    FROM source_table
)
INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3
FROM latest_records
WHERE row_num = 1;

このクエリは、source_tableテーブルからtarget_tableテーブルにレコードを挿入しようとします。latest_records CTE は、source_tableテーブルの各パーティション (col1 および col2 列でグループ化) の最新レコードのみを含む一時テーブルを作成します。INSERTステートメントは、latest_records CTE から target_tableテーブルにレコードを挿入します。WHERE句は、各パーティションの最新レコードのみが挿入されるようにフィルター処理します。

トランザクションを使用して、一連の操作を原子単位として実行できます。これにより、操作の一部が失敗した場合でも、すべての操作が成功または失敗するようにすることができます。トランザクションを使用して重複を回避するには、次のとおりに使用できます。

-- source_table テーブルから target_table テーブルにレコードを挿入し、
-- 競合を回避するためにトランザクションを使用する
BEGIN TRANSACTION;

-- target_table テーブルから重複レコードを削除する
DELETE FROM target_table
WHERE col1 IN (
    SELECT col1
    FROM source_table
);

-- source_table テーブルから target_table テーブルにレコードを挿入する
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table;

COMMIT;

このクエリは、source_tableテーブルからtarget_tableテーブルにレコードを挿入しようとします。BEGIN TRANSACTIONステートメントは、トランザクションを開始します。DELETEステートメントは、source_tableテーブルに挿入されるレコードと一致する target_tableテーブルから重複レコードを削除します。INSERTステートメントは、source_tableテーブルからtarget_tableテーブルにレコードを挿入します。COMMITステートメントは、トランザクションをコミットします。トランザクションが失敗すると、ROLLBACKステートメントを使用してトランザクションをロールバックできます。