バッチでSQLServerのSELECTの結果をCSVで出力する必要があったのでメモ。

使用ツール

・テキストエディタ
・SQLServer環境

手順

テキストエディタで下記のように記述しましょう。ファイル名はtest.batにしました。

サーバー認証方法がSQLServer認証の場合

test.bat

sqlcmd -S svname1 -d dbname1 -U user1 -P test1 -i test.sql -v param='9999' -b -s, -W -o test.csv

サーバー認証方法がWindows認証の場合

test.bat

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がバッチファイルが置いてあるディレクトリにあると判断されますが、タスクスケジューラーを用いて定時に実行するなどするときは、ファイル名は絶対パスで書いてないと実行されません。

テキストエディタで下記のように記述しましょう。ファイル名はtest.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
)

作成したバッチファイルをダブルクリックしてみましょう。test.csvにSELECT結果が出力されています。

-oオプションではなく、2>&1を使用しても、同じように結果が出力されます。

test.bat

sqlcmd -S localhost -E -i test.sql -v param='9999' -b -s, -W > test.csv 2>&1