【SQL Server】bcpを使用したインポートとエクスポート
テストする際にテーブルのデータを一括でインポート・エクスポートする場合の方法の一つとしてbcpを使用することがありますが、オプションやフォーマットファイル出力のやり方をよく忘れるので覚書。
フォーマットファイルについては、bcpだけでなくBULK INSERTにも使えます。
bcpはコマンドラインからの実行になるので、バッチなどを作っておいて、簡単なテーブルバックアップ用のスクリプトなども作っておくと便利かと思います。
フォーマットファイルの作成
エクスポートやインポートをする対象テーブルのフォーマットファイルを作成します。
Syntax :
bcp table_or_view format nul -c -x -f formatfile.xml -t, -S server_name\instance_name -U login_id -P password -d database_name
Option :
| -f | format_file |
| -S | server_name [\instance_name] |
| -U | login_id |
| -P | password |
| -d | database_name |
| -t | field_term(default \t) |
その他詳しいオプションは下記を参照してください。
https://msdn.microsoft.com/ja-jp/library/ms162802.aspx
実際に使う際は次のようにコマンドプロンプトで実行します。
bcp dbo.EMPLOYEES format nul -c -x -f formatfile.xml -t, -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales
"-f"で指定するフォーマットファイルは、出力先パスとファイル名を記載してください。
出力されたフォーマットファイルはxml形式となります。
bcpによる一括エクスポート
先ほど作成したフォーマットファイルを使用してテーブルのデータを全てエクスポートします。
bcp dbo.EMPLOYEES out .\EMPLOYEES.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_output.log
"-o"によって出力されるのは処理ログで、"out"によって指定されたパスにエクスポート結果が出力されます。
"-f"で指定するフォーマットファイルは格納先を指定してください。
処理ログ
エクスポート結果（ヘッダーはつけられません）
テーブルを指定するのではなく、SQLとqueryoutを使用して出力対象をいじることも可能です。
WHERE句を使って条件に合うデータのみ抽出する等に使えるかと。
bcp "SELECT TOP(10) * FROM dbo.EMPLOYEES" queryout .\EMPLOYEES_TOP10.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_TOP10_output.log
bcpによる一括インポート
作成したフォーマットファイルを使用して、データファイルをテーブルに全てインポートできます。
"-i"で指定するインポート用のデータファイルは、格納パスとファイル名を指定してください。
bcp dbo.EMPLOYEES in .\EMPLOYEES.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_input.log
処理ログ
