個人でSQLServerでSQLの実行結果を確認したいときは、ManagementStudioでできます。
今回はお客様から毎日同じ時間にあるSQLを実行した結果が確認したいと要望がありました。
出力結果はcsvファイルで所定のフォルダに格納してほしいとのことでした。
いろいろ検討した結果、sqlcmdでSELECT結果をcsv形式(カンマ区切り)で出力するバッチを作成して、Windowsのタスクスケジューラーに登録して実行することにしました。
今回はその時の対応について書こうと思います。
sqlcmdでSELECT結果をcsv出力
サンプルデータ
最終的にはヘッダありで出力することになりましたがヘッダありヘッダなしのどちらで対応するかでsqlcmdのオプションが違ってきます。
ヘッダありの場合
ヘッダが必要な場合は、以下のコマンドで取得できます。
sqlcmd -S インスタンス名 -E -d TEST -i test.sql , -b -s, -W -o test.csvsqlcmdのオプション
| -S | サーバー名 |
| -E | Windows認証 |
| -i | 入力ファイル(出力したいSQLを記載) |
| -b | エラーが発生した場合は、バッチを中止 |
| -s | 列の区切り文字csvの場合は,(カンマ)を指定する。 |
| -W | 余分なスペースを削除 |
| -o | 出力ファイル |
| -d | データベース名 |
実行するtest.sqlの中身は以下のように記述しています。
[test.sql]
set nocount on
SELECT * FROM table1set no count onで処理行出力を非表示にする
上記コマンドで実行すると最終行に(x行処理されました。)と出力されてしまいます。
set no count onを付与すると出力されないようになります。
test.csvの出力結果
ヘッダは必要だが2行目の——を消したい
ヘッダありの場合は、オプション：-hを付与せずに実行すればOKですが、一つ問題があります。
それは2行目に—-が出力されてしまう。。。。
いろいろ考えた結果、今回はヘッダー用とデータ用に分割して実行し、最後にtypeコマンドで結合することにしました。
そこでヘッダー無しで出力します。
ヘッダなしで出力する
ヘッダなしの場合はsqlcmdのオプションで-h -1にすれば、ヘッダが消えます。
[test.bat]
sqlcmd -S インスタンス名 -E -d TEST -i test.sql , -b -s, -W -h -1 -o test.csv| オプション | 説明 |
|---|---|
| -h | -1にするとヘッダーを出力しない |
これでヘッダーは消えました。
ヘッダー作成用のSQLを作成
次にヘッダーのみを出力する用のSQLを作成します。(出力ファイル名はheader.csv)
ヘッダ作成用SQL
--ヘッダー用SQL
set nocount on SELECT 'col1','col2','col3','col4',''col5実データ用のSQLを作成
続いて実データを取得する用のSQLを作成します。(出力ファイル名はtest.csv)
データ作成用SQL
set nocount on SELECT * FROM table1ヘッダーのcsv実データのcsvファイルをtypeコマンドで結合する
上記SQLをsqlcmdで実行し出力されたファイルをtypeコマンドで結合します。
ヘッダ作成用SQLとデータ作成用SQLを実行して結合するバッチ
rem ヘッダ用
sqlcmd -S インスタンス名 -E -d TEST -i heder.sql , -b -s, -W -h -1 -o header.csv
rem データ用
sqlcmd -S インスタンス名 -E -d TEST -i test.sql , -b -s, -W -h -1 -o test.csv
rem ヘッダーとデータを連結してKEKKA.csvに出力
type header.csv test.csv >KEKKA.csvtype header.csv test.csv >KEKKA.csv
type ファイル1 ファイル2でファイルが結合されてその結果をリダイレクトで出力しています。
typeコマンドは簡単にファイルの結合ができるので便利です。
KEKKA.csvの出力結果
２行目の—-が削除されてヘッダも存在する。
まとめ
- set no count onで不要な行メッセージを出力しないようにできる。
- ヘッダなしの場合は、sqlcmdのオプションで -h -1にする。
※この方法はバッチしばりの方法でPowershellなどでやるともっと簡単にファイル作成ができます。
私の環境ではPowershellを動かすことが環境だったのでバッチで実行しています。
powershellでやりたい場合は以下を参考にしてみてください。
コメント