2018年12月30日日曜日

SQL Server のBCPコマンドによるデータエクスポート

SQL Server 2012 でBCPコマンドを使ったデータエクスポートの手順をまとめる。
BCPコマンドの仕様詳細は以下のリンクを参照。
bcp ユーティリティ
bulk copy program ユーティリティ (bcp) では、 Microsoft SQL Server のインスタンスと、ユーザー指定の形式のデータ ファイルとの間でデータの一括コピーを行います。 bcp
ユーティリティを使うと、多数の新規行を SQL Server テーブルにインポートしたり、データをテーブルからデータ
ファイルにエクスポートしたりできます。 このユーティリティでは の知識は必要ありません。ただし、 queryout
Transact-SQLオプションと同時に使う場合はその知識が必要になります。
データをテーブルにインポートするには、そのテーブル用に作成されたフォーマット
ファイルを使用するか、テーブルの構造およびテーブルの列に有効なデータの型を理解しておく必要があります。
このようにBCPコマンドを使ってデータをDBにInsertしたり、DBからデータを取り出したり出来ます。
今回は、DBから指定のテーブルデータをテキストファイルに出力するサンプルを作成します。

環境

  • DB: SQL Server 2012 SP4 (11.0.7001.0 (X64)) Evaluation or Express
  • データソース: AdventureWorksLT2012
  • データ保存先:ローカル ファイル(テキスト ファイル)
※BCPコマンドはEXPRESSでも使用できる。

サンプル1

AdventureWorksLT2012 のAddressテーブルのデータをテキストファイルに書き出します。
DB:AdventureWorksLT2012 (Evaluation)
Table:Address

Power Shell 実行内容
PS C:\Users\Hoge> bcp AdventureWorksLT2012.SalesLT.Address  out "c:\Temp\Address.txt" -S 192.168.1.50\SQLEVA -T -c -t","

コピーを開始しています...

450 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (450000.00 行/秒)

使用したオプション(大文字/小文字の区別がある)
コマンド 説明
out 出力するファイルのフルパス
-S [ServerName][Instanse Name]
-T Windows認証の場合に指定
-c 文字データとして処理する場合に指定
-t 列の区切りを指定

出力結果
※先頭行に列名が入らないことに注意

enter image description here

SQLEXPRESSの場合でも実行できる
PS C:\Users\Hoge> bcp AdventureWorksLT2012.SalesLT.Address  out "c:\Temp\Address_EXP.txt" -S hego-pc\SQLEXPRESS -T -c -t","

コピーを開始しています...

450 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (450000.00 行/秒)

サンプル2

AdventureWorksLT2012 のAddressテーブルに対するSELECTクエリの結果をテキストファイルに書き出します。
DB:AdventureWorksLT2012 (Evaluation)
Table:Address

SELECTクエリ:
SELECT * FROM AdventureWorksLT2012.SalesLT.Address WHERE City = 'London'

Power Shell 実行内容
PS C:\Users\hoge> bcp " SELECT * FROM AdventureWorksLT2012.SalesLT.Address WHERE City = 'London' " queryout "c:\Temp\Address_query.txt" -S 192.168.1.50\SQLEVA -T -c -t","

コピーを開始しています...
14 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計 : 1 平均 : (14000.00 行/秒)

使用したオプション(大文字/小文字の区別がある)
コマンド 説明
queryout 出力するファイルのフルパス
-S [ServerName][Instanse Name]
-T Windows認証の場合に指定
-c 文字データとして処理する場合に指定
-t 列の区切りを指定

※SELECTクエリを使ってデータを抽出する場合は書き出すファイルのコマンドを「out」ではなく「queryout」を指定する。

出力結果
※先頭行に列名が入らないことに注意

enter image description here

まとめ

BCPコマンドを使ってSQL Server のデータをテキストファイルに出力するサンプルを作成してみました。
さらに細かい設定は「format」指定をしてXMLのフォーマットファイルに指定できる模様です。
SSMSの結果をコピペでEXCELに貼ることしかやったことありませんでしたので勉強になりました…
使うか?と言われると微妙。

参考

bcp ユーティリティ

bcp ユーティリティを使用した一括データのインポートとエクスポート (SQL Server)

【SQL Server】bcpを使用したインポートとエクスポート




0 件のコメント:

コメントを投稿