SQLServerで用意されているSQLを用いて、指定した列の値をキーにして重複チェックし、重複するレコードがなければINSERTし、あった場合はINSERTしない、という処理のSQLの紹介です。
LEFT JOINで重複チェックする方法を用いても可能ですが、今回はMERGEを用いることにします。
複数人で開発を行っている場合などはMERGEを使う方法の方がソースを見たときに直観的に何をしているか伝わりやすいと思います。
サンプルの動作確認環境
- Windows10 64bit
- SQLServer2017 Express
サンプルSQLの仕様
サンプルSQL
INSERTされるテーブルの準備
テーブル名はT_Tagertとしています。
CREATE TABLE [T_Target](
[id] [int] IDENTITY(1,1) NOT NULL,
[Item1] [nvarchar](max) NULL,
[Item2] [nvarchar](max) NULL,
[Item3] [nvarchar](max) NULL
);
INSERT INTO [T_Target]
([Item1]
,[Item2]
,[Item3])
VALUES
('01',
'01',
'01');
重複チェックしINSERTするSQL
Item1、Item2列の組み合わせで重複チェックを行い、重複がなければINSERTを行います。INSERTするデータはUNIONでまとめています。
With InsertData as (
SELECT '01' Item1, '01' Item2, '02' Item3
UNION
SELECT '01' Item1, '02' Item2, '01' Item3
UNION
SELECT '02' Item1, '01' Item2, '01' Item3
)
MERGE ST_DB.dbo.T_Target AS TargetData
USING InsertData
ON TargetData.Item1 = InsertData.Item1 AND TargetData.Item2 = InsertData.Item2
WHEN NOT MATCHED BY TARGET
THEN INSERT (Item1, Item2, Item3)
VALUES (InsertData.Item1, InsertData.Item2, InsertData.Item3);
補足：MERGEについて
上記ではMERGEで、
T_Target（上記サンプルの更新される側のテーブル）に同じデータが存在しない場合
にどうするかだけを定義しましたが、本来の使い方として
- T_Targetにだけデータが存在する場合
- T_Targetに同じデータが存在する場合
それぞれのケースで何をするか定義することも可能です。↓のような構文となります。
MERGE {変更対象テーブル}
USING {参照テーブル}
ON {存在有無を確認する式}
WHEN MATCHED
THEN {両方のテーブルに存在する場合の処理}
WHEN NOT MATCHED BY TARGET
THEN {変更対象テーブルに存在しない場合の処理}
WHEN NOT MATCHED BY SOURCE
THEN {参照テーブルに存在しない場合の処理}ここでは、T_Target2というテーブルを用意し、Item1列、Item2列の組み合わせで重複チェックしたうえで、
- T_Target2には同じデータが存在しない場合はINSERT
- T_Target2にだけデータが存在する場合はDELETE
- T_Target2にも同じデータが存在する場合UPDATE
を行うサンプルSQLを紹介します。
まず、下のSQLを実行してT_Target2テーブルを準備してください。
CREATE TABLE [T_Target2](
[id] [int] IDENTITY(1,1) NOT NULL,
[Item1] [nvarchar](max) NULL,
[Item2] [nvarchar](max) NULL,
[Item3] [nvarchar](max) NULL
);
INSERT INTO [T_Target2]
([Item1]
,[Item2]
,[Item3])
VALUES
('01','01','01'),
('02','02','01');
以下が上記仕様のSQLとなります。
With InsertData as (
SELECT '01' Item1, '01' Item2, '02' Item3
UNION
SELECT '01' Item1, '02' Item2, '01' Item3
UNION
SELECT '02' Item1, '01' Item2, '01' Item3
)
MERGE ST_DB.dbo.T_Target2 AS TargetData
USING InsertData
ON TargetData.Item1 = InsertData.Item1 AND TargetData.Item2 = InsertData.Item2
WHEN MATCHED
THEN UPDATE
SET
TargetData.Item3 = InsertData.Item3
WHEN NOT MATCHED BY TARGET
THEN INSERT (Item1, Item2, Item3)
VALUES (InsertData.Item1, InsertData.Item2, InsertData.Item3)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;以上となります。
