読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【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"で指定するフォーマットファイルは、出力先パスとファイル名を記載してください。

f:id:utiowa:20170323192637p:plain

出力されたフォーマットファイルはxml形式となります。
f:id:utiowa:20170323192646p:plain


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"で指定するフォーマットファイルは格納先を指定してください。

処理ログ
f:id:utiowa:20170323192807p:plain

エクスポート結果(ヘッダーはつけられません)
f:id:utiowa:20170323192752p:plain

テーブルを指定するのではなく、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

f:id:utiowa:20170323192825p:plain

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

処理ログ
f:id:utiowa:20170323193651p:plain

【参考】
https://msdn.microsoft.com/ja-jp/library/aa337544.aspx

広告を非表示にする