OUTPUT 句 の紹介

SQL Server の MVP を受賞したということで、
SQLWorld★大阪#10 で SQL Server 2012 Express の紹介をしました - お だ のスペース
で紹介している T-SQL をもう少し掘り下げて説明していこうかと思います。
最初は、OUTPUT 句の紹介を。

SQL Server 2005 から追加されました。
OUTPUT 句 (Transact-SQL)

INSERT/UPDATE/DELETE/(MERGE SQL Server 2008 から追加) で追加/更新/削除 された行の情報を取得出来ます。

使いどころとしては、ID 列(Identity) や 計算列 の値を取得したり、更新操作で影響を受けたデータを次の処理で使ったり…等が主だと思います。

それでは、クエリを見ながら解説を。

use [テスト]

-- 準備
create table [マスタ] ( 
  [Id] int identity(1,1) not null primary key, 
  [名前] nvarchar(10) not null, 
  [名前長さ] as len([名前]) 
)
go

insert into [マスタ] values ('さとう')
insert into [マスタ] values ('たなか')
insert into [マスタ] values ('すずき')
go

テーブルを作成して、テストデータを INSERT します。

insert into [マスタ] 
output inserted.* 
values ( 'あべ' ) 
select @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('マスタ')

update [マスタ] set [名前] = '旧姓たなか' 
output deleted.*, inserted.* 
where [名前] = 'たなか'

delete from [マスタ] 
output deleted.* 
where [名前] = 'すずき'

OUTPUT 句では、INSERTED/DELETED で影響を受けた列を取得します。

• INSERTED:INSERT/UPDATE で使用可能。追加/更新された後のデータを取得する際に使用する接頭辞
• DELETED:UPDATE/DELETE で使用可能。更新/削除される前のデータを取得する際に使用する接頭辞

INSERT では、追加された行のデータを
UPDATE では、更新される前と更新後のデータを
DELETE では、削除される前のデータを
それぞれ取得することが出来ます。
INSERT した結果の ID 列や計算列の値、UPDATE した場合に計算列がどう変わったのかも取得出来ます。

OUTPUT 句すげー！

-- 複数件の同時更新も可能
insert into [マスタ] output inserted.* 
select [名前] from [マスタ]
select @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('マスタ')

update [マスタ] set [名前] = '****'
output deleted.*, inserted.*

delete from [マスタ]
output deleted.*

複数件をまとめて更新する場合でも、使用することが出来ます。
INSERT された ID 列(Identity) の結果を取得する方法として、
@@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('') があります。多分よく使われているのは、SCOPE_IDENTITY() だと思います。*1
@@IDENTITY (Transact-SQL)
SCOPE_IDENTITY (Transact-SQL)
IDENT_CURRENT (Transact-SQL)

これらは最後の1行しか返しませんので、上のようなクエリ(INSERT-SELECT)や SQL Server 2008 で追加された VALUES をカンマ区切りで複数個渡すクエリ(INSERT VALUES (), (), ...))で複数行の追加を行うと最後の1行の ID 値しか取得することは出来ません。
OUTPUT 句 を利用すると、複数行の ID 値も取得する事が出来ます。

OUTPUT 句すげー！

-- INTO を使って テーブル/テーブル変数 に結果を入れることも可能
declare @更新結果 table ( 
  [Seq] int identity(1,1), 
  [Action] nchar(3), 
  [変更前_Id] int, 
  [変更前_名前] nvarchar(10), 
  [変更前_名前長さ] int, 
  [変更後_Id] int, 
  [変更後_名前] nvarchar(10), 
  [変更後_名前長さ] int 
)

insert into [マスタ] output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
values ('さとう'), ('たなか'), ('すずき')

insert into [マスタ] 
output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
values ( 'あべ' ) 

update [マスタ] set [名前] = '旧姓たなか' 
output 'UPD', deleted.*, inserted.* into @更新結果
where [名前] = 'たなか'

delete from [マスタ] 
output 'DEL', deleted.* into @更新結果 ( [Action], [変更前_Id], [変更前_名前], [変更前_名前長さ] )
where [名前] = 'すずき'

insert into [マスタ] output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
select [名前] from [マスタ]

update [マスタ] set [名前] = '****'
output 'UPD', deleted.*, inserted.* into @更新結果

delete from [マスタ]
output 'DEL', deleted.* into @更新結果 ( [Action], [変更前_Id], [変更前_名前], [変更前_名前長さ] )

select * from @更新結果
go

OUTPUT 句で取得出来る結果を テーブルやテーブル変数に追加する事も出来ます。
これを利用することで、

• 更新されたデータだけを対象に次の処理を行う
• 履歴テーブルに退避

等々が出来ます。

OUTPUT 句すげー！

SQL Server 2005 以降を利用しているなら、SCOPE_IDENTITY() は使わずに OUTPUT 句を利用しましょう！
今回は、MERGE については省きます。MERGE の時に OUTPUT 句も絡めて説明する予定です。

ちなみに C# から、1件 INSERT した結果の IDENTITY 列を取るコードはこんな感じ

トランザクション/例外処理は省略

using (var conn = new SqlConnection("〜"))
using (var cmd = new SqlCommand(@"insert into [マスタ] output inserted.[Id] values ( 'たなか' ) ", conn))
{
  conn.Open();
  // ExecuteNonQuery ではなく、ExecuteScalar を使用して、結果を受け取る
  var id = cmd.ExecuteScalar();
  Console.WriteLine(id);
  conn.Close();
}

ストアドだけではなく、C# 等から発行するアドホックなクエリでも当然使えますので、使えるところでは迷わず OUTPUT 句を使いましょう！

クエリ全文

use [テスト]

-- 準備
create table [マスタ] ( 
  [Id] int identity(1,1) not null primary key, 
  [名前] nvarchar(10) not null, 
  [名前長さ] as len([名前]) 
)
go

insert into [マスタ] output inserted.* 
values ('さとう'), ('たなか'), ('すずき')
select @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('マスタ')
go

-- マスタのデータ変更
insert into [マスタ] 
output inserted.* 
values ( 'あべ' ) 
select @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('マスタ')

update [マスタ] set [名前] = '旧姓たなか' 
output deleted.*, inserted.* 
where [名前] = 'たなか'

delete from [マスタ] 
output deleted.* 
where [名前] = 'すずき'

-- 複数件の同時更新も可能
insert into [マスタ] output inserted.* 
select [名前] from [マスタ]
select @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('マスタ')

update [マスタ] set [名前] = '****'
output deleted.*, inserted.*

delete from [マスタ]
output deleted.*

-- INTO を使って テーブル/テーブル変数 に結果を入れることも可能
declare @更新結果 table ( 
  [Seq] int identity(1,1), 
  [Action] nchar(3), 
  [変更前_Id] int, 
  [変更前_名前] nvarchar(10), 
  [変更前_名前長さ] int, 
  [変更後_Id] int, 
  [変更後_名前] nvarchar(10), 
  [変更後_名前長さ] int 
)

insert into [マスタ] output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
values ('さとう'), ('たなか'), ('すずき')

insert into [マスタ] 
output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
values ( 'あべ' ) 

update [マスタ] set [名前] = '旧姓たなか' 
output 'UPD', deleted.*, inserted.* into @更新結果
where [名前] = 'たなか'

delete from [マスタ] 
output 'DEL', deleted.* into @更新結果 ( [Action], [変更前_Id], [変更前_名前], [変更前_名前長さ] )
where [名前] = 'すずき'

insert into [マスタ] output 'INS', inserted.* into @更新結果 ( [Action], [変更後_Id], [変更後_名前], [変更後_名前長さ] )
select [名前] from [マスタ]

update [マスタ] set [名前] = '****'
output 'UPD', deleted.*, inserted.* into @更新結果

delete from [マスタ]
output 'DEL', deleted.* into @更新結果 ( [Action], [変更前_Id], [変更前_名前], [変更前_名前長さ] )

select * from @更新結果
go
-- 後片付け
drop table [マスタ]
go

*1:それぞれ微妙に動きが違うので、使い方誤らないで！