【SQL】OUTPUT句の便利な使い方~データ更新前後の処理結果を確認する~

目次

はじめに

OUTPUT句で何ができるのでしょう?
INSERTやUPDATEした際、SQLServerでは{〇行処理されました}とメッセージが出ますよね。
その処理した結果の値を確認したいことはないですか?
もしくは、処理結果をログテーブルに登録するとか。
毎回SELECTして取得しても良いですが、OUTPUT句を使うとそれが一発でできるみたいです。
(SQL Server 2005以降)

OUTPUT句の使いどころ

OUTPUT句は、INSERT(追加)、UPDATE(更新)、DELETE(削除) された(影響を受けた)行もしくは列の情報を取得出来ます。

使いどころとしては、更新操作で影響を受けたデータをログテーブルに登録する、更新した値を次の処理で使用する…など。

主に例を挙げてみましょう。
以下のような社員テーブルがあったとします。

SyainMstテーブル

社員番

氏名 生年月日
1000 山田 ハナコ 19900101
1002 山田 ジロウ 19950203

【INSERT】挿入された新規行を取得する

社員マスタに行をinsertし、挿入した行を取得する

INSERT INTO SyainMst(社員番号, 氏名, 生年月日)
     OUTPUT inserted.*
     VALUES ('1001', '山田 タロウ', 19950203)

処理結果:

社員番号氏名生年月日
1001山田 タロウ19950203

特定の列のみを抽出する場合は、inserted.* に列を指定する

INSERT INTO SyainMst(社員番号, 氏名, 生年月日)
     OUTPUT inserted.社員番号
     VALUES ('1001', '山田 タロウ', 19950203)

処理結果:

社員番号
1001

OUTPUT句で取得した処理結果をLOGテーブルに登録する

(更新前)LOGテーブル
ID 社員番号
1 1000
2 1002
INSERT INTO SyainMst(社員番号, 氏名, 生年月日)
     OUTPUT ’3’,inserted.社員番号
    INTO LOG (ID, 社員番号)
     VALUES ('1001', '山田 タロウ', 19950203)

処理結果:

(更新後)LOGテーブル
ID 社員番号
1 1000
2 1002
3 1001

【DELETE】削除された行を取得する

社員マスタから社員番号1000を削除し、削除行の社員番号、氏名列を取得する

DELETE FROM SyainMst
     OUTPUT deleted.社員番号
           ,deleted.氏名
      WHERE 社員番号= '1000'

☆特定の列のみを抽出する場合は、inserted.* に列を指定する

社員番号氏名
1000山田 ハナコ

【UPDATE】更新された行の新旧の値を取得する

社員マスタから氏名山田ハナコの生年月日を19990707に更新し、更新前の生年月日、更新後の生年月日を取得する

UPDATE SyainMst
   SET 生年月日 = 19990707
OUTPUT inserted.社員番号
      ,deleted.生年月日
      ,inserted.生年月日
 WHERE 氏名 = '山田 ハナコ'

☆更新された行の新旧の値を取得する場合は、inserted.*, deleted.*,と組み合わせて取得する

社員番号生年月日生年月日
10001990010119990707

今日はここまで。