ITコーディネータのシュウです。

お久しぶりです。あっという間にゴールデンウィークも終盤になってしまいました。写真は、去年も行った加須市玉敷神社の騎西藤まつりに行ったときの写真です。きれいでしたね！

話しは変わりますが、GW中に息子を連れて近くの温水プールに行ったところ、プロのインストラクターと思われるお姉さんが現れて、これからエクササイズをします！とのこと。プールに来ていた大半の方（おばちゃんやおばあちゃんが多かったけど）が参加するようだったので、ものは試しにと一緒に参加しました。そうしたら、みっちり1時間、水の中でステップを踏んだり手足をいろいろと動かしてのエクササイズ、結構疲れました！！　体力無いな～。　他の方は慣れているのか、おばちゃんやおばあちゃんが元気にこなしているのを見ると、すごいというか、女性は強し。自分ももっと体力を付けないと！と、強く感じさせられました。

＜本日の題材＞
Bcpユーティリティ（SQL Server）

前回、ORACLEのUTL_FILEパッケージを取り上げました。PL/SQLでテキストファイルの読み書きができるというものですが、SQL ServerのT-SQLで同様のことができるかどうかを調べてみたところ、xp_cmdshellというシステム拡張ストアドプロシージャを使用して、bcpコマンドなどを呼び出してテーブルのデータをテキストファイルへ書き出したり、テキストファイルのデータをテーブルに読込んだりすることは可能という情報がありました。ただし、このストアドプロシージャxp_cmdshellは様々なOSコマンドを実行できるということから、セキュリティ的にはとても危険だということで、デフォルトでは無効となっています。これを有効にする方法もあるとのことですが、それは次の機会に回すとして、今回は、bcpコマンドのほうを取り上げてみたいと思います。

BCPユーティリティでは、テーブルやビューのデータをテキストに出力する、テキストデータをテーブルへ取込むなど、一般にエクスポート／インポートと言われる内容ができますが、queryoutオプションを使用すると、指定したクエリーの結果を出力することが可能になります。

構文は、以下：

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"]
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 | 110)]
   [-w]
   [-x]

※パラメータはアルファベット順。

ちなみに、コマンドプロンプト画面で、bcp /? と実行すると以下のような画面が出ます。

構文の上記のパラメータを全て説明するのはとても大変なので、詳細はMicrosoft社のコマンドの説明のHPに任せて、サンプルを使ってよく使う箇所について説明したいと思います。

１）テーブルやビューのデータをテキストファイルに出力する

※SQL ServerにはWindows認証でアクセスするとします。Windows認証の場合は -T を使用します。BLOGデータベースの「商品マスタ」のデータを、「c:\temp」ディレクトリに「商品マスタ.txt」という名前のファイルにカンマ区切りで出力します。

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ.txt" -c -t, -S サーバ名 –T

作成されたテキストファイルを確認してみると、

商品マスタテーブルのデータが出力されています。
カンマ区切りではなく、カンマの代わりに空白で試してみると

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ2.txt" -c –t  -S サーバ名 –T

 作成された中身を確認すると、

たしかにカンマ区切りではなく、空白で区切られていることが確認できました。

※ちなみに、SQL Server認証では、-T ではなく、-U ユーザ名 –P パスワードとなります。

２）テキストファイルのデータをテーブルに取込む

先ほど出力したテキストファイルをテーブルに読込んでみます。
まず、商品マスタテーブルのデータをtruncateコマンドで削除します。

次に、bcpコマンドでテキストファイルを読込みます。パラメータとして、out ではなく in を使います。

bcp BLOG.dbo.商品マスタ in "C:\temp\商品マスタ.txt" -c -t, -S サーバ名–T

 テーブルの中身を確認してみると、
SELECT * FROM dbo.商品マスタ;

先ほどデータを削除した後、bcpコマンドによってテキストファイルからデータが取込まれたことが確認できました。

３）テーブルやビューから、指定したクエリで取得したデータをテキストファイルに出力する

今度は、テーブルやビューのデータを全て出力するのではなく、指定した条件でSELECTした結果をテキストファイルに出力する場合です。
これは、bcpコマンドの後に、”で囲んだSELECT文を記載し、パラメータとして out ではなく queryout を使用します。例として、商品マスタの分類が「果物」のデータをテキストファイルに出力してみます。

bcp "SELECT 商品CD, 商品名, 分類, 値段 FROM BLOG.dbo.商品マスタ WHERE 分類='果物'" queryout "C:\temp\商品マスタ3.txt" -c -t, -S サーバ名 –T

作成されたテキストファイルを確認します。

指定したSELECT文の条件通りの結果がテキストファイルに出力されていることが確認できました。

今日は以上まで

にほんブログ村