SQLServerでSELECT結果をCSVで出力する

SQLSQL Server
この記事は約4分で読めます。
スポンサーリンク

個人でSQLServerでSQLの実行結果を確認したいときは、ManagementStudioでできます。

今回はお客様から毎日同じ時間にあるSQLを実行した結果が確認したいと要望がありました。
出力結果はcsvファイルで所定のフォルダに格納してほしいとのことでした。

いろいろ検討した結果、sqlcmdでSELECT結果をcsv形式(カンマ区切り)で出力するバッチを作成して、Windowsのタスクスケジューラーに登録して実行することにしました。

今回はその時の対応について書こうと思います。

sqlcmdでSELECT結果をcsv出力

サンプルデータ

SQL

最終的にはヘッダありで出力することになりましたがヘッダありヘッダなしのどちらで対応するかでsqlcmdのオプションが違ってきます。

ヘッダありの場合

ヘッダが必要な場合は、以下のコマンドで取得できます。

sqlcmd -S インスタンス名 -E -d TEST -i test.sql , -b -s, -W -o test.csv

sqlcmdのオプション

-Sサーバー名
-EWindows認証
-i入力ファイル(出力したいSQLを記載)
-bエラーが発生した場合は、バッチを中止
-s列の区切り文字csvの場合は,(カンマ)を指定する。
-W余分なスペースを削除
-o出力ファイル
-dデータベース名

実行するtest.sqlの中身は以下のように記述しています。

[test.sql] 
set nocount on 
SELECT * FROM table1

set no count onで処理行出力を非表示にする

上記コマンドで実行すると最終行に(x行処理されました。)と出力されてしまいます。

set no count onを付与すると出力されないようになります。

test.csvの出力結果

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にするとヘッダーを出力しない
headerなし
headerなしで出力した場合

これでヘッダーは消えました。

ヘッダー作成用の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.csv

type header.csv test.csv >KEKKA.csv

type ファイル1 ファイル2でファイルが結合されてその結果をリダイレクトで出力しています。
typeコマンドは簡単にファイルの結合ができるので便利です。

KEKKA.csvの出力結果

結果

2行目の—-が削除されてヘッダも存在する。

まとめ

  • set no count onで不要な行メッセージを出力しないようにできる。
  • ヘッダなしの場合は、sqlcmdのオプションで -h -1にする。

※この方法はバッチしばりの方法でPowershellなどでやるともっと簡単にファイル作成ができます。
 私の環境ではPowershellを動かすことが環境だったのでバッチで実行しています。

powershellでやりたい場合は以下を参考にしてみてください。

コメント

タイトルとURLをコピーしました