[SQL_Server]bcpユーティリティと「xp_cmdshell」の利用。

【目的】ストアドから、SELECT結果をファイル出力してみよう。

ってコトで、調べつつ組んでみた。
結論から言うと、「xp_cmdshell」は権限がなくて使えなかった。
（ローカルの SQL Server ではなくて、共有のテストサーバーを利用しているので、権限変更できない）

でも、折角なのでメモ。

--*--* bcpユーティリティ
　「bcpユーティリティは、一度に１つのテーブルを処理するコマンドラインユーティリティです。」
　と説明されています。
SQL Server 2005 ストアドプロシージャプログラミングに書いてある。


　この「bcp」を使うと、DBのテーブルからデータをテキストファイルにエクスポート（出力）することが可能。
　また、ファイルからDBにインポートすることも可能らしい。（試していない）
　bcpユーティリティ詳細(MSDN)


　1.　先ず、基本的な形のコマンドを流す。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--

-- 特定テーブルを一括出力

bcp database名.schema名.table名 out "ファイル出力先パス(ファイル名)" -U username -P password
　↓
bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


　上記は、
　dbHogeというデータベースのdbo(デフォルトのスキーマ名)というスキーマにある、
　tbHuge というテーブルのデータを、SQL ServerのあるPC(ローカル)の
　【C:\】ドライブ直下に『ListBackUp.csv』というファイル名の CSVファイルに出力する。
　ちなみに、指定したファイル名のファイルが既に存在すれば、内容を上書き(元の内容は削除)
　して、存在しなければ新規に作成して書き込んでくれる。

　ローカルではなく、ネットワーク上の SQL Server とデータをやりとりするには、
　「-S」オプションをつけてサーバを指定する必要がある。
　この場合のファイル名は、コマンド発行したPC(自分のPC)のパスを指す。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -S 192.168.xx.ｘｘ -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


　オプションは他にも色々ある。必要に応じて追記したらいいと思う。
　詳細はMSDNで。

　2.上記のコマンドを実行すると、出力形式について対話形式で確認が行われる。
　　指定したテーブルのフィールド数分。

　　確認される質問は以下な感じ。アタクシがやってみた例。

--------------------------------------------------------------------------------
"フィールド cColA [nchar] のファイルストレージ型を入力してください"
　⇒「nchar」と入力してEnter押下
"フィールド cColA [1] のプレフィックス長を入力してください"
　⇒「1」と入力してEnter
"フィールド ターミネータ [none] を入力してください"
　⇒何も入力しないでEnter
--------------------------------------------------------------------------------


　↑は、フィールド一つ分の確認。これが１セット。
　指定したテーブルのフィールドが１０個あれば、これが１０回繰り返される。
　「ファイルストレージ型」と「プレフィックス長」については、フィールドのデータ型
　などで変わるので、全て同じではない。

　3.この確認が終わると、最後の質問がある。


--------------------------------------------------------------------------------
"このフォーマット情報をファイルに保存しますか？ [y/n]"
　⇒「y」と入力してEnter、もっかいEnter
--------------------------------------------------------------------------------


　すると、ファイル出力先に指定したパスに、「ListBackUp.csv」と「bcp.fmt」という
　ファイルが作成される。
　「ListBackUp.csv」を開いてみると、思ったようにきれいにはデータはできていない。

　4.これを整形するために、「bcp.fmt」を編集する。

　　「bcp.fmt」をメモ帳(NotePad)で開く。

============================================================

9.0
5
1       SQLNUMERIC    1       19      ""   1     cColA              Japanese_CI_AS
2       SQLNUMERIC    1       19      ""   2     vcColB             Japanese_CI_AS
3       SQLTINYINT    0       1       ""   3     tiColC             ""
4       SQLDATETIME   0       8       ""   4     dtColD             ""
5       SQLNUMERIC    1       19      ""   5     cColE              Japanese_CI_AS

============================================================

　↑さっき、上記の対話確認の結果作成された内容(らしい)。

１行目　：「9.0」 ⇒ バージョン
2行目　：「5」フィールド数
3行目～：フィールドの出力形式

　3行目以降に各々色づけしたのは以下の説明のため。

●1 ファイルでの、フィールド番号
●2 フィールドの　データ型
●3 フィールドの　プレフィックス長
●4 フィールドの　データサイズ
●5 フィールドの　終端文字
●6 DBテーブル内での、フィールド番号
●7 DBテーブル内での、フィールド名
●8 フィールドの　照合順序

　正直、「nchar」と入力した項目(1, 2, 5) が『SQLNUMERIC』となっているのは
　よくわからん。なぜにニューメリック。

　とにもかくにも、コレを以下のように編集と思うように出力される（た）。
　太文字。

============================================================

9.0
5
1       SYBCHAR    0       19      ","       1     cColA              ""
2       SYBCHAR    0       19      ","       2     vcColB             ""
3       SYBCHAR    0       1       ","       3     tiColC             ""
4       SYBCHAR    0       8       ","       4     dtColD             ""
5       SYBCHAR    0       19      "\r\n"    5     cColE              ""

============================================================

　フィールド番号1～4 の終端文字を「,」にしたのは、 CSVファイルにしたかったから。
　好きなものにしたらいい。
　フィールド番号5 は、改行コード。データ１行毎に改行したいので。

　6.編集したファイルを名前を付けて保存する。
　　名称は仮に『OutputList.fmt』にする。保存先はCドライブ直下。

　7.編集した『OutputList.fmt』をフォーマットオプションで指定して、データ出力してみる。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -f C:\OutputList.fmt -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


ちょっと中断。編集中。

　テーブルの一括出力だけでなく、オプションでクエリを利用することも可能。
　ほしいデータだけを出力できる。
　ワタクシが使ってみたのは以下な感じ。

[SQL_Server]レコードが○件以上あり、かつ□日以上経過したレコードの抽出(削除)

こんな要望がありました。

　操作ログを、一定期間で削除して欲しい。
　でも、レコードが100件以内だったら、一定期間が経過していても、
　そのユーザーのレコード(操作ログ)は削除しないでほしい。

ちなみに、この対象テーブルには「No列」のようなものはありません。
前回【ROW_NUMBER()を使ってみる】で使用したテーブルです。
これです。

--*--*  操作ログテーブル  *--*--*--*--*--
テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation    DATETIME    -- 操作日時
　　　　　　cUserID        NCHAR(8)    -- 操作ユーザーID
　　　　　　cProgramID     NCHAR(8)    -- 操作画面ID
　　　　　　vcContents     NVARCHAR(2) -- 操作種別コード
--*--*--*--*--*--*--*--*--*--*--*--*--*--

で、前回の「ROW_NUMBER()」が登場するワケです。
とりあえず、こんな感じで。
いきなり削除しちゃうのはアレなんで、まずは抽出してみる。
抽出結果が、「削除したいものである」と確認できたら、
01行目の 'SELECT *' を 'DELETE' に直せばOK。

※エディタな感じで 行に番号付けてみた。

------------------------------------------------------------------------------------------
01:  SELECT * 
02:    FROM tbOperationLog
03:   WHERE  EXISTS( SELECT *
04:                    FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY cUserID
05:                                                         ORDER BY dtOperation ASC ) AS RNum
06:                                 ,dtOperation
07:                                 ,cUserID
08:                                 ,cProgramID
09:                                 ,vcContents
10:                             FROM tbOperationLog ) Obj
11:                   WHERE tbOperationLog.cUserID = Obj.cUserID
12:                     AND tbOperationLog.dtOperation = Obj.dtOperation
13:                     AND Obj.RNum > 100
14:                     AND DATEDIFF( DAY, Obj.dtOperation, CURRENT_TIMESTAMP ) > 90 )
------------------------------------------------------------------------------------------

解説：
１つ目のポイントは、03行目の「EXISTS」で条件を指定しているトコロ。
-- というか、要件を満たす条件を指定するには、と考えたトキ、この方法しか思いつかなかった。

２つ目のポイントは、03-10行目で「EXISTS」の条件を、更にサブクエリにしているトコロ。
先ずサブクエリ(04行の「SELECT～」-10行目)で、
仮想の行番号を「ROW_NUMBER()」で付与してテーブルを作ってあげる。
この時点で、ユーザー毎に日付順で行番号が配される。(詳しくは前回を参照)

で、このテーブルを「EXISTS」の条件で SELECTして、
「WHERE句」条件(13行目)で仮想の行番号を指定( Obj.RNum > 100 )する。
こうすることで、100件以内のレコードは条件より外される。

-- ちなみに、サブクエリとして使用しないと、仮想の行番号を「WHERE句」に指定することができない。
-- ソコで初めて作成されるのだから、このQuery内では条件には使えないよね。

あとは、「DATEDIFF()」関数(返り値はINT)で削除期間を指定してあげる。
上記の場合、
DATEDIFF( 日付を, Obj.dtOperationから, 本日日時を引いた ) 数値が、'90' より大きい( > 90 )
なら、という条件になる。


Query一発でできるなんて、ステキング。

　
　
　
　

[SQL_Server]ROW_NUMBER() を使ってみる。

SQL Serverでは、'2005'から「ROW_NUMBER()」という機能(関数)を実装したらしい。
私は使ったことないが、Oracleのソレと同じ(？)ようなものみたい。

MSDN⇒ ROW_NUMBER (Transact-SQL)

簡単に言うと、「ROW_NUMBER()」は SELECT した結果(レコードの集合)に対して、
指定された条件で順位付けして番号を振ってくれる便利関数。
ちなみに、「OVER()句」とセットで使用。条件は、この「OVER( 条件 )」に書く。


先月より触り始めた SQL Server の Stored Procude で、これを使う機会がありそうなので、
ちょいと練習で使ってみた。
以下は、操作履歴テーブル【tbOperationLog】を条件なしに(全件)抽出したもの。
※ここで使用するテーブル、カラムはあくまでサンプル。


テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation -- 操作日時
　　　　　　cUserID -- 操作ユーザーID
　　　　　　cProgramID -- 操作画面ID
　　　　　　vcContents -- 操作種別コード


サンプル その1：

SELECT ROW_NUMBER() OVER( ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
-- ORDER BY vcContents
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01

解説：
「ROW_NUMBER()」は、直後の「OVER( ORDER BY dtOperation ASC )」を条件に順位付けを行う。
サンプル1 では『操作日時( dtOperation )』の昇順( ASC )で順位付けを行った。

ちなみに、コメントアウトで無効にしている最終行の【ORDER BY vcContents】を有効にすると、
順位は「OVER()句」で指定された条件で行われるが、表示される順序がこっちで行われる。


結果：(外側のORDER BY を有効にした場合)

RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03

で、条件指定にはもう少し便利なものがある。
「PARTITION BY」というもの。
「ORDER BY 句」の直前に指定する。
私もコレは初めて知ったものなので、とりあえず使ってみる。

サンプル その2：

SELECT ROW_NUMBER() OVER( PARTITION BY cUserID ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-07-19    BLOSSOMS    C2008086    03
1         2008-06-26    SAKURA39    A2008085    02
2         2008-07-19    SAKURA39    C2008086    03
3         2008-07-27    SAKURA39    C2008086    00
4         2008-08-02    SAKURA39    C2008086    01

解説：
「PARTITION BY」を指定すると、順位付けを行うグループを設定できる。
サンプル2 は「操作ユーザーID( cUserID )」内で、『操作日時( dtOperation )』の昇順( ASC )に順位付けを行った。


もちろん、「ORDER BY 句」は降順( DESC )の指定もできますよ。