SQL Server - OUTPUT 句の使い方

SQL Server の OUTPUT 句

SQL Server の OUTPUT 句を使うと、INSERT、UPDATE、DELETE、MERGE ステートメントで影響を受けたレコードを取得することができます。

トリガーをご存知の方は、トリガーの定義の中で inserted や deleted のカラムにアクセスできると思いますが、同じような感じで値を取得することができます。

今回は INSERT、UPDATE、DELETE の際に、影響のあったレコードを取得して、テーブル変数に入れてみましょう。

INSERT ステートメントで OUTPUT 句を使う

まずは INSERT ステートメントで OUTPUT 句を使ってみましょう。

次のような Student テーブルにレコードを 2 つ INSERT します。

OUTPUT 句を使って、その時に生成された　IDENTITY カラムの StudentID の値と挿入した FirstName, LastName の値を @StudentInserted という名前のテーブル変数に取得するクエリーは以下の通りです。

DECLARE @StudentInserted TABLE (
   StudentID INT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
);

-------------------------------------------

INSERT INTO Student
   (FirstName, LastName, Gender)
OUTPUT 
   inserted.StudentID, 
   inserted.FirstName, 
   inserted.LastName
INTO @StudentInserted (
   StudentID,
   FirstName,
   LastName
)
VALUES 
   ('Rin', 'Yokota', 'M'),
   ('Hina', 'Yokota', 'F');

-------------------------------------------

SELECT	*
FROM	@StudentInserted;

OUTPUT 句は INSERT INTO と VALUES の間に入ります。

INSERT ステートメントでは inserted のカラムプレフィックスを使って、挿入された値を取得することができます。 deleted は使えません。

IDENTITY の StudentID カラムのように、直接値を挿入していないカラムからも値を取得できます。

UPDATE ステートメントで OUTPUT 句を使う

次は UPDATE ステートメントで OUTPUT 句を使ってみましょう。

Student テーブルの Gender が M のレコードの LastName を大文字に更新し、影響のあったレコードの変更前後の値を @StudentUpdated という名前のテーブル変数に取得するクエリーは以下の通りです。

DECLARE @StudentUpdated TABLE (
   StudentID INT,
   LastNameFrom VARCHAR(50),
   LastNameTo VARCHAR(50)
);

-------------------------------------------

UPDATE	Student
SET	LastName = UPPER(LastName)
OUTPUT	inserted.StudentID, deleted.LastName, inserted.LastName
INTO	@StudentUpdated		
WHERE	Gender = 'M';

-------------------------------------------

SELECT	*
FROM	@StudentUpdated;

OUTPUT 句は SET と WHERE (FROM がある時は FROM) の間に入ります。

UPDATE ステートメントでは deleted のカラムプレフィックスを使って変更前の値を、inserted のカラムプレフィックスを使って変更後の値を取得することができます。

DELETED ステートメントで OUTPUT 句を使う

最後に DELETE ステートメントで OUTPUT 句を使ってみましょう。

StudentID が 6 より大きいレコードを削除して、削除されたレコードの値を @StudentDeleted という名前のテーブル変数に取得するクエリーは以下の通りです。

DECLARE @StudentDeleted TABLE (
   StudentID INT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
);

-------------------------------------------

DELETE	S
OUTPUT	deleted.StudentID, 
	deleted.FirstName, 
	deleted.LastName
INTO @StudentDeleted (
	StudentID,
	FirstName,
	LastName
	)
FROM	Student AS S
WHERE	StudentID > 6;

-------------------------------------------

SELECT	*
FROM	@StudentDeleted;

OUTPUT 句は DELETE と FROM table_source の間に入ります。

DELETE ステートメントでは deleted のカラムプレフィックスを使って、削除されたレコードの値を取得することができます。 inserted は使えません。

今回は取得したデータを後で使う前提で OUTPUT INTO でテーブル変数に値を入れましたが、INTO を指定しなければ OUTPUT で指定したカラムの値がそのまま結果セットとして返ります。

なお、OUTPUT 句で取得できる値は INSERT、UPDATE、 DELETE ステートメントが実行された後、トリガーが走る前の値です。

対象のテーブルにトリガーがついていて、トリガーが走った後の値が必要な場合は、OUTPUT 句で同時に取得するのではなく、後ほど別途取得してください。