2017-02-14

OUTPUT 句で FK 付テーブルに INTO する時のメモ

SQL Server

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 [ユーザー]

2017-02-05

2/18(土) 第19回中国地方DB勉強会 in 米子で SQL Server / T-SQL のお話しします

SQL Server 勉強会

第19回中国地方DB勉強会 in 米子 - connpass

中国地方DB勉強会初参加ー！楽しみです。
鳥取行くの初めてじゃないかな？

初めて参加する勉強会なので、お話しする内容をちょっと悩んでます。タイトル通りの T-SQL のお話しで良いのか、SQL Server 寄りの方が良いのか…。

是非ご参加を～。

2017-01-10

1/24(火) SQLWorld★大阪#40 開催します

勉強会 SQL Server

2週間前の告知！ SqlWorld :: SQLWorld★大阪#40 開催します。22回目の平日夜開催で、前回同様ハンズオン形式行う予定です。

【日時】
2017年1月24日（火曜日）　19:00~21:00

【イベント概要】
SQLWorld 22回目の平日夜開催～。今回も、みんなで SQL を書いてみようというハンズオン企画です！ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号グランフロント大阪タワーB（オフィス）

【参加費】
無料

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)

【参加可能人数】
13 人

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を～。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。

2016-12-18

第４回　関西DB勉強会懇親会で LT してきましたー #dbkan

勉強会 SQL Server

本編も良かったですが、懇親会も楽しかったー

Lightning talks starting with @shinsukeoda of @SQLWorld_JP giving update on @SQLServer @OpenAtMicrosoft #dbkan pic.twitter.com/DDxW49Ftqn
— Colin Charles (@bytebot) 2016年12月17日

スライドはこちら

2016年を振り返って from Oda Shinsuke

今年リリースされた SQL Server 関係の物を拾ってきただけです。

03/31 Developer Edition が無償に
Microsoft SQL Server Developer Edition is now free | SQL Server Blog
05/11 Azure SQL DB Elastic pool リリース
Azure SQL Database elastic pools now generally available | Blog | Microsoft Azure
06/01 SQL Server 2016 リリース※ページ消えてる
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/
07/12 Azure SQL Data Warehouse リリース
The elastic future of data warehousing | SQL Server Blog
11/16 SQL Server on Linux (vNext) 公開
Announcing SQL Server on Linux public preview, first preview of next release of SQL Server | SQL Server Blog
11/16 SQL Server 2016 SP1 リリース
SQL Server 2016 Service Pack 1 generally available | SQL Server Blog
11/17 JDBC Driver for SQL Server の OSS 化
Open sourcing the Microsoft JDBC Driver and Maven support – Microsoft JDBC Driver for SQL Server Team Blog
12/16 vNext CTP 1.1 公開
SQL Server next version Community Technology Preview 1.1 now available | SQL Server Blog

後半の勢いやばいですねー、触れる時間作らないと。。

2016-12-16

メモ：PowerQuery の数式ドキュメント

Power Query (informally known as "M") formula categories

英語版しかなさそう。。

2016-12-01

Oracle にも OFFSET FETCH が入ってたので Doma の対応コード書いてみた。

Oracle SQL Server Java

仕事で Oracle12c を触る機会があって気付いたのですが、Oracle12c から OFFSET FETCH が入ってたようです。
第37回新しいSQLについて
構文が SQL Server と似た感じですね。
※正確な構文はこちらから SELECT

SQL Server との違いで ORACLE の方が便利そうな箇所は、

ORDER BY が無くてもOK (普通 ORDER BY 書くけどね)
OFFSET は省略可 (先頭からN件とる場合)
FETCH で指定する方法が行数と割合(%)で指定可 (全体の5%とか)
fetch で with ties を指定すると、最後のデータと同じものは指定行数を超えても含まれる。

SQL Server の方が便利そうな箇所は、

FETCH を省略可 (先頭N件をスキップして残り全部とる場合)

使うかどうかは置いといて機能的には負けてますね。。

で、Welcome to Doma — Doma 2.0 ドキュメントも使ってたので、12C用の PagingTransformer 書いてみました。
雰囲気動きそうな感じです。percent や with ties には対応していませんが、ちょっと使う分にはこれで良いかなー。

/*
 * Copyright 2004-2010 the Seasar Foundation and the Others.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific language
 * governing permissions and limitations under the License.
 */
package org.seasar.doma.internal.jdbc.dialect;

import static org.seasar.doma.internal.Constants.ROWNUMBER_COLUMN_NAME;

import org.seasar.doma.internal.jdbc.sql.node.FragmentNode;
import org.seasar.doma.internal.jdbc.sql.node.FromClauseNode;
import org.seasar.doma.internal.jdbc.sql.node.SelectClauseNode;
import org.seasar.doma.internal.jdbc.sql.node.SelectStatementNode;
import org.seasar.doma.internal.jdbc.sql.node.WhereClauseNode;
import org.seasar.doma.jdbc.SqlNode;

/**
 * @author taedium
 * 
 */
public class Oracle12PagingTransformer extends OraclePagingTransformer {

    public Oracle12PagingTransformer(long offset, long limit) {
        super(offset, limit);
    }

    @Override
    public SqlNode visitSelectStatementNode(SelectStatementNode node, Void p) {
        if (processed) {
            return node;
        }
        processed = true;

        OrderByClauseNode originalOrderBy = node.getOrderByClauseNode();
        OrderByClauseNode orderBy = node.getOrderByClauseNode();
        if (originalOrderBy != null) {
            orderBy = new OrderByClauseNode(originalOrderBy.getWordNode());
            for (SqlNode child : originalOrderBy.getChildren()) {
                orderBy.appendNode(child);
            }
        } else {
            orderBy = new OrderByClauseNode("");
        }

        if (this.offset > 0) {
            orderBy.appendNode(new FragmentNode(" offset "));
            orderBy.appendNode(new FragmentNode(String.valueOf(this.offset)));
            orderBy.appendNode(new FragmentNode(" rows"));
        }

        if (this.limit > 0) {
            orderBy.appendNode(new FragmentNode(" fetch first "));
            orderBy.appendNode(new FragmentNode(String.valueOf(this.limit)));
            orderBy.appendNode(new FragmentNode(" rows only"));
        }

        SelectStatementNode result = new SelectStatementNode();
        result.setSelectClauseNode(node.getSelectClauseNode());
        result.setFromClauseNode(node.getFromClauseNode());
        result.setWhereClauseNode(node.getWhereClauseNode());
        result.setGroupByClauseNode(node.getGroupByClauseNode());
        result.setHavingClauseNode(node.getHavingClauseNode());
        result.setOrderByClauseNode(orderBy);
        result.setForUpdateClauseNode(node.getForUpdateClauseNode());
        result.setOptionClauseNode(node.getOptionClauseNode());
        return result;
    }
}