今回の記事は、SQL-Server 2008 (Express)における、MERGEクエリーについて書かせて頂きたいと思います。
古いバージョンであるSQL-Server 2000では、二つのテーブルを一つに併合(マージ)したいなぁと思ったら、INSERT文とUPDATE文に分けて処理する必要がありました。
SQL-Server 2008 (Express)からは、ついに一文で書く事が出来るMERGE文が誕生しました。
たとえば、#T_TABLE1と#T_TABLE2という2つのテーブルがあったとして、#T_TABLE2の内容を#T_TABLE1に併合するためには、従来は以下のように書く必要がありました。
-- --------------------------
-- テストで使ったテーブル
-- --------------------------
CREATE TABLE #T_TABLE1
(
ITEM_CD int,
ITEM_NM nvarchar(40),
SURYO int,
CONSTRAINT T_TABLE1_MAIN_KEY Primary Key (ITEM_CD)
);
CREATE TABLE #T_TABLE2
(
ITEM_CD int,
ITEM_NM nvarchar(40),
SURYO int,
CONSTRAINT T_TABLE2_MAIN_MAIN_KEY Primary Key (ITEM_CD)
);
INSERT INTO #T_TABLE1
(ITEM_CD,ITEM_NM,SURYO)
VALUES(111111,'TEST-1',100),(222222,'TEST-2',200),(444444,'TEST-4',400),(777777,'TEST-7',700);
INSERT INTO #T_TABLE2
(ITEM_CD,ITEM_NM,SURYO)
VALUES(111111,'TEST-1',1),(333333,'TEST-3',3),(555555,'TEST-5',5),(666666,'TEST-6',6);
-- ----------------------
-- MERGE文が無かった場合
-- ----------------------
--重複したデータは更新
UPDATE M
SET
M.SURYO = T.SURYO
FROM #T_TABLE1 AS M
INNER JOIN #T_TABLE2 AS T
ON(M.ITEM_CD = T.ITEM_CD)
WHERE M.SURYO <> T.SURYO;
--不一致データは挿入
INSERT INTO #T_TABLE1
(ITEM_CD,ITEM_NM,SURYO)
SELECT
M.ITEM_CD,
M.ITEM_NM,
M.SURYO
FROM #T_TABLE2 AS M
LEFT JOIN #T_TABLE1 AS T
ON(M.ITEM_CD = T.ITEM_CD)
WHERE T.ITEM_CD IS NULL;
-- ----------------------
-- MERGE文
-- ----------------------
MERGE #T_TABLE1 AS M
USING #T_TABLE2 AS T
ON(M.ITEM_CD = T.ITEM_CD)
WHEN MATCHED THEN
UPDATE
SET
M.SURYO = T.SURYO
WHEN NOT MATCHED THEN
INSERT (ITEM_CD,ITEM_NM,SURYO)
VALUES(T.ITEM_CD,T.ITEM_NM,T.SURYO);
MERGE文であれば、こんなに短く書く事が出来るんです。
ちなみに、INSERT文やUPDATE文などは、文末にセミコロン(;)が無くともエラーにならないのですが、新しく誕生したMERGE文は文末にセミコロンが無いと容赦なくエラーになりますので注意が必要です。
それと、このMERGE文は、ただマージしてくれるだけでも便利で助かるのですけれど、さらにその先があります。
なんと、どういう挙動をしたのかログを書く事が出来るんです。
どのデータをUPDATEして、どれをINSERTしたんだ、というのが一発で分かるわけです。
ログのテーブルは自分で自由に設計できるので、使いやすいようにしておけるのも魅力。
-- -----------------------------
-- ログ用テーブルの定義
-- -----------------------------
CREATE TABLE #T_LOG
(
SEQ int IDENTITY(0,1),
MERGE_DT datetime2,
MERGE_KEY int,
MERGE_ACTION nvarchar(6),
CONSTRAINT T_LOG_MAIN_KEY Primary Key (SEQ)
);
-- ----------------------
-- MERGE文
-- ----------------------
MERGE #T_TABLE1 AS M
USING #T_TABLE2 AS T
ON(M.ITEM_CD = T.ITEM_CD)
WHEN MATCHED THEN
UPDATE
SET
M.SURYO = T.SURYO
WHEN NOT MATCHED THEN
INSERT (ITEM_CD,ITEM_NM,SURYO)
VALUES(T.ITEM_CD,T.ITEM_NM,T.SURYO)
OUTPUT
SYSDATETIME() AS "MERGE_DT",
INSERTED.ITEM_CD AS "MERGE_ITEM", --DELETED.ITEM_CDだと更新したキーのみ抽出できます
$action AS "MERGE_ACTION"
INTO #T_LOG;
$actionという変数を参照すると、UPDATEされたのか、INSERTされたのかを知る事が出来ます。
同じ処理を作りこもうとすると、やはりそれなりのコードが必要ですから、MERGE文の便利さが身に染みる瞬間です。
会社では、まだSQL-Server 2000なのが辛い・・・。
企業用のすごいバージョンなんですけど、書けるクエリーが無償版の2008 Expressに全然負けるし、SQLCLRも使えないしなぁ・・・。でも頑張るぞ。
