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

個人でSQL ServerのSQL実行結果を確認したい場合は、Management Studioを使えば簡単に確認できます。

しかし、今回は「お客様から毎日決まった時間に特定のSQLを実行し、その結果をCSVファイルで所定のフォルダに保存してほしい」という要望がありました。

いろいろ検討した結果、sqlcmdでSELECT結果をCSV形式（カンマ区切り）で出力するバッチファイルを作成し、Windowsのタスクスケジューラーに登録して自動実行する方法を採用しました。

この記事では、その時の対応方法をまとめます。

sqlcmdでSELECT結果をcsv出力

サンプルデータ

SQL Serverのテーブル（例：table1）からデータをCSVで出力したいとします。

ヘッダーありで出力してみる

ヘッダー（列名）を含めてCSV出力したい場合は、以下のようなコマンドで取得できます。

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

主なオプション解説：

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

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

1.set nocount onで「○行処理されました」を非表示にする

sqlcmdで実行すると、最終行に「(x 行処理されました。)」というメッセージが出力されてしまいます。
これを防ぐには、SQLの先頭でset nocount onを記述します。

test.csvの出力結果

2.ヘッダーは必要だが2行目の「——」を消したい

ヘッダーありで出力すると、2行目に「——」が出力されてしまいます。

この問題を回避するために、ヘッダー用とデータ用でSQLを分けて実行し、最後にファイルを結合する方法を採用しました。

ヘッダー用SQLとデータ用SQLを別々に作成して結合する

1.ヘッダなしの実データ取得バッチを作成

ヘッダーを出力したくないので、-h -1オプションを指定します。

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

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

2,ヘッダーのみ出力するSQL（header.sql）のバッチを作成する

列名として設定したい名前をSELECTの後に書きます。実データと列数は一致させてください。

 --ヘッダー用SQL 
set nocount on SELECT 'col1','col2','col3','col4',''col5

ヘッダーのcsv実データのcsvファイルをtypeコマンドで結合する

それぞれのSQLをsqlcmdで実行し、出力されたファイルをtypeコマンドで結合します。

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

KEKKA.csvの出力結果

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

8. まとめ

• set nocount onで不要な「○行処理されました」メッセージを非表示にできる。
• ヘッダーなしの場合は、-h -1オプションを使う。
• ヘッダーとデータを分けて出力し、typeコマンドで結合することで、2行目の「——」を回避できる。

※この方法はバッチしばりの方法でPowershellなどでやるともっと簡単にファイル作成ができます。

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

📝 PowerShellでSQL ServerのSELECT結果をCSV出力する方法【断片化率を取得】

SQL ServerのSELECT結果をPowerShellでCSV出力する方法を解説。インデックスの断片化率取得から、バッチファイルによる自動実行までをわかりやすく紹介。sqlcmdとの比較や応用例も掲載。

kajiblo.com

2021.03.21