あるテーブルに対して、レコードがあればUPDATEし、なければINSERTするというのをMERGEを使ってやってみました。 UPSERTと呼ばれたりするやつですね。
準備
とりあえず適当なテーブルを作ります。本のタイトル、値段を管理するBookテーブルを作りました。初期データも入れておきます。
SQLを書いてみる
本のId、タイトル、値段をパラメータ化してSQLを実行することにします。 Idが同じレコードが既に存在したらUPDATE、そうでなければINSERTします。 できたSQLがこれ。
MERGE Book AS target -- 入力値から仮のテーブルsourceを作る USING (SELECT @Id, @Title, @Price) AS source (Id, Title, Price) -- UPDATE or INSERTを判断する条件 ON (target.Id = source.Id) WHEN MATCHED THEN -- 条件に一致したときの処理 UPDATE SET Title = source.Title, Price = source.Price WHEN NOT MATCHED THEN -- 条件に一致しなかったときの処理 INSERT (Id, Title, Price) VALUES (source.Id, source.Title, source.Price);
INSERTしてみる
まだテーブルに存在しない本を追加してみます。さっきのSQLだとまだパラメータが定義されていないので、パラメータを定義して値をセットします。
DECLARE @Id INT DECLARE @Title NVARCHAR(max) DECLARE @Price INT SET @Id = 4 SET @Title = 'More Effective C#' SET @Price = 1980 MERGE Book AS target -- 入力値から仮のテーブルsourceを作る USING (SELECT @Id, @Title, @Price) AS source (Id, Title, Price) -- UPDATE or INSERTを判断する条件 ON (target.Id = source.Id) WHEN MATCHED THEN -- 条件に一致したときの処理 UPDATE SET Title = source.Title, Price = source.Price WHEN NOT MATCHED THEN -- 条件に一致しなかったときの処理 INSERT (Id, Title, Price) VALUES (source.Id, source.Title, source.Price);
実行結果。ちゃんとINSERTになってますね。
UPDATEしてみる
先ほど追加した本の価格が変わったという想定で、値段を変えて実行してみます。今度はすでにId = 4のレコードが存在するのでUPDATEになるはず。
DECLARE @Id INT DECLARE @Title NVARCHAR(max) DECLARE @Price INT SET @Id = 4 SET @Title = 'More Effective C#' SET @Price = 2980 MERGE Book AS target -- 入力値から仮のテーブルsourceを作る USING (SELECT @Id, @Title, @Price) AS source (Id, Title, Price) -- UPDATE or INSERTを判断する条件 ON (target.Id = source.Id) WHEN MATCHED THEN -- 条件に一致したときの処理 UPDATE SET Title = source.Title, Price = source.Price WHEN NOT MATCHED THEN -- 条件に一致しなかったときの処理 INSERT (Id, Title, Price) VALUES (source.Id, source.Title, source.Price);
実行結果。More Effective C#の値段が1980から2980に変わりました。 今回はINSERTではなくUPDATEになっていますね。
まとめ
MERGEを使ってUPSERTをやってみました。他にもレコードがあればDELETE、なければINSERTとかも実装できそうですね。