OUTPUT 句 (Transact-SQL)
今日気付いたのですが、INTO 先のテーブルには FOREIGN KEY 付いてるとダメという制約がありました。
幾つかの回避方法があると思いますが、ここでは テーブル変数 に退避してからという方法のメモです。
※FK を一旦無効にしてから OUTPUT で INTO したあと再度 FK 有効にするってやり方もあります。
対処方法のメモ
create table [ユーザー] (
[ID] int not null primary key,
[名前] nvarchar(10) not null
)
create table [通知] (
[ID] int not null primary key,
[種類] nvarchar(10) not null,
[内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
[ユーザーID] int not null references [ユーザー]([ID]),
[通知ID] int not null references [通知]([ID]),
[日時] datetime2(0) not null,
primary key([ユーザーID], [通知ID])
)
insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')
insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか?')
, (2, N'定期', N'二郎さん。お元気ですか?')
, (3, N'定期', N'三郎さん。お元気ですか?')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')
insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')
select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]
ってのがあったときに、アラート通知の紐づけは別テーブルにしよう!と変更したとします。
テーブルを作って、
create table [ユーザーへのアラート通知] (
[ユーザーID] int not null references [ユーザー]([ID]),
[通知ID] int not null references [通知]([ID]),
[日時] datetime2(0) not null,
primary key([ユーザーID], [通知ID])
)
データを移行します。
delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知]
where exists (
select * from [通知]
where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
and [通知].[種類] = N'アラート'
)
これがエラーになります。
メッセージ 332、レベル 16、状態 1、行 99
OUTPUT INTO 句の対象のテーブル 'ユーザーへのアラート通知' を、(主キー、外部キー) リレーションシップのどちらにも配置できません。参照制約 'FK__ユーザーへのアラー__ユーザーI__6477ECF3' が見つかりました。
FK があるとダメーと言われました。
というわけで、一旦テーブル変数に INTO してから移行します。
declare @t table (
[ユーザーID] int not null,
[通知ID] int not null,
[日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t
where exists (
select * from [通知]
where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t
select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]
出来上がり!
クエリ全文
create table [ユーザー] (
[ID] int not null primary key,
[名前] nvarchar(10) not null
)
create table [通知] (
[ID] int not null primary key,
[種類] nvarchar(10) not null,
[内容] nvarchar(100)
)
create table [ユーザーへの通常通知] (
[ユーザーID] int not null references [ユーザー]([ID]),
[通知ID] int not null references [通知]([ID]),
[日時] datetime2(0) not null,
primary key([ユーザーID], [通知ID])
)
insert into [ユーザー] ([ID], [名前])
values (1, N'一郎'), (2, N'二郎'), (3, N'三郎')
insert into [通知] ([ID], [種類], [内容])
values (1, N'定期', N'一郎さん。お元気ですか?')
, (2, N'定期', N'二郎さん。お元気ですか?')
, (3, N'定期', N'三郎さん。お元気ですか?')
, (4, N'アラート', N'三郎さん。不明な端末でログイン試行されました。')
, (5, N'セキュリティ', N'三郎さん。パスワードリセット要求。')
, (6, N'アラート', N'一郎さん。アカウントロックされました。')
insert into [ユーザーへの通常通知] ([ユーザーID], [通知ID], [日時])
values (1, 1, '2017-02-01 12:34:56')
, (2, 2, '2017-02-02 20:02:01')
, (3, 3, '2017-02-03 02:11:48')
, (3, 4, '2017-02-07 19:02:32')
, (3, 5, '2017-02-07 19:09:51')
, (1, 6, '2017-02-08 12:45:20')
select * from [ユーザー]
select * from [通知]
select * from [ユーザーへの通常通知]
go
create table [ユーザーへのアラート通知] (
[ユーザーID] int not null references [ユーザー]([ID]),
[通知ID] int not null references [通知]([ID]),
[日時] datetime2(0) not null,
primary key([ユーザーID], [通知ID])
)
go
delete from [ユーザーへの通常通知]
output deleted.* into [ユーザーへのアラート通知]
where exists (
select * from [通知]
where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
and [通知].[種類] = N'アラート'
)
go
declare @t table (
[ユーザーID] int not null,
[通知ID] int not null,
[日時] datetime2(0) not null
)
delete from [ユーザーへの通常通知]
output deleted.* into @t
where exists (
select * from [通知]
where [通知].[ID] = [ユーザーへの通常通知].[通知ID]
and [通知].[種類] = N'アラート'
)
insert into [ユーザーへのアラート通知]
select * from @t
select * from [ユーザーへの通常通知]
select * from [ユーザーへのアラート通知]
go
drop table [ユーザーへのアラート通知]
drop table [ユーザーへの通常通知]
drop table [通知]
drop table [ユーザー]