バッチでSQLServerのSELECTの結果をCSVで出力する必要があったのでメモ。
使用ツール
・テキストエディタ
・SQLServer環境
手順
- バッチファイルを作成する。
- SQLファイルを作成する
- 実行！
1. バッチファイルを作成する。
テキストエディタで下記のように記述しましょう。ファイル名はtest.batにしました。
サーバー認証方法がSQLServer認証の場合
sqlcmd -S svname1 -d dbname1 -U user1 -P test1 -i test.sql -v param='9999' -b -s, -W -o test.csv
サーバー認証方法がWindows認証の場合
sqlcmd -S localhost -E -i test.sql -v param='9999' -b -s, -W -o test.csv
パラメータの意味
| オプション (パラメータ) |
説明 | 指定値 |
|---|---|---|
| -S | サーバ名 | svname1 |
| -E | ユーザー名とパスワードを使用せずに セキュリティ接続を使用して SQL Server にサインイン |
- |
| -d | DB名 | dbname1 |
| -U | ユーザ名 | user1 |
| -P | パスワード | test1 |
| -i | SQLファイル名 | test.sql |
| -v | 変数 | param='9999' |
| -b | エラーが発生したときに、sqlcmd を終了し、 DOS ERRORLEVEL 値を返すようにする。 |
- |
| -s | 区切り文字を指定する。 アンパサンド (&)、セミコロン (;) など、 特別な意味を持つ文字を使用する場合は、 ダブルクォーテーション (") で囲む。 |
- |
| -W | 後続の空白を削除する。 | - |
| -o | 出力するファイルを指定する。 | - |
注意点
バッチファイルをダブルクリックして実行する場合は、test.sqlやtest.csvがバッチファイルが置いてあるディレクトリにあると判断されますが、タスクスケジューラーを用いて定時に実行するなどするときは、ファイル名は絶対パスで書いてないと実行されません。
2. SQLファイルを作成する
テキストエディタで下記のように記述しましょう。ファイル名はtest.sqlにしました。
set nocount on
select
*
from
testtable
where
id = $(param)
set nocount off
解説
set nocount on
set nocount off
この記述がないと、出力したCSVファイルに「n件処理されました。」と出力されてしまいます。
逆に必要ならこの記述は省きましょう。
where
id = $(param)
- バッチファイルを作成する。で作成したバッチファイルに記載した、
param='9999'をwhere句に指定しています。
ちなみにバッチファイルに、param='9998', '9999'と記載して、where句を以下のように記載すると、複数のidを検索することもできます。
where
id in ( $(param) )
ただ、paramが長すぎると実行時にエラーになってしまうのでそういう時は違うSQLを考えましょう。
sqlに条件句がいらないときはバッチファイルから-v param='9999'を省いてください。
よくわからなかった点
ここに「※実行結果を返したいときはSQLをEXITで囲むといい」と記載があり、やってみたところエラーになってしまいました...なぜ。。。よくわからないのでまた別途調べます。
EXIT(
set nocount on
select
*
from
testtable
where
id = $(param)
set nocount off
)
3. 実行！
作成したバッチファイルをダブルクリックしてみましょう。test.csvにSELECT結果が出力されています。
ちなみに
-oオプションではなく、2>&1を使用しても、同じように結果が出力されます。
sqlcmd -S localhost -E -i test.sql -v param='9999' -b -s, -W > test.csv 2>&1
参考サイト
SQLServerでselectの結果をCSVで出力する方法。
SQLServerのDBをバッチファイルでバックアップ-パート２
batファイルでSQLServerのSelect結果をCSV形式で出力する。
バッチファイルからsqlcmd(SQLServer)の戻り値を取得！なんちゃって