SQL Serverでプロシージャなどを作っていると、プロシージャから直接テキストファイルの入出力を行いたくなることがあります。

普通こういうことはクライアントでやるべきなんですが、バッチ的に処理したいという時にはプロシージャでという話になりがちです。

しかし、Oracleにはテキストファイル入出力のためにUTL_FILEパッケージというものが用意されていますが、SQL Serverには同様の機能はありません。

では、どうやってテキストファイルの入出力を行うかというと．．．

やっぱり外部コマンドに頼るしかないみたいです．．．

まず、テキストファイルの出力であれば、"[SQL Server] SQLからOSコマンドを実行する方法" で例に挙げているような方法が最も簡単です。

EXEC master..xp_cmdshell 'echo file output test > test.txt'

ログファイルに情報を書き出す場合なんかであれば、この方法で十分でしょう。

しかし、この方法は入力にはまず使えないし、出力でも例えばテーブルの内容やクエリーの実行結果をCSV形式で出力するなどといったことをやるにはちょっと使いにくいです。

そういうときにはbcpユーティリティというものを使います。

bcpユーティリティはOracleで言うSQL*Loaderみたいなもので、固定長ファイルやCSVファイルをテーブルにアップロードしたり、逆にテーブルの内容やクエリーの実行結果を固定長ファイルやCSVファイルにダウンロードしたりするツールです。

bcpユーティリティによるテキストファイルのアップロード

bcpユーティリティを使ってテキストファイルをテーブルにアップロードするには、まず、固定長またはCSVなどの区切り文字形式のファイルと、そのフォーマットファイルが必要です。

例えば、

TABLE1
  COL1  VARCHAR(1)
  COL2  VARCHAR(10)
  COL3  VARCHAR(100)

こんなテーブルに

(test.csv)
  1,A,あ
  2,BB,いい
  3,CCC,ううう

こんなCSVファイルをアップロードしたい場合には

(test.fmt)
  8.0
  3
  1  SQLCHAR  0  1    ","     1  COL1  Japanese_CS_AS
  2  SQLCHAR  0  10   ","     2  COL2  Japanese_CS_AS
  3  SQLCHAR  0  100  "\r\n"  3  COL3  Japanese_CS_AS

こんなフォーマットファイルを用意します。

そして、

bcp db1.dbo.TABLE1 in test.csv -Usa -Psa -SSERVER1 -ftest.fmt

  -U：ログイン名
  -P：パスワード
  -S：SQLServerインスタンス名
  -f：フォーマットファイル名

こんなコマンドを実行すればいいのです。

これをSQLから直接実行するならば、

EXEC master..xp_cmdshell 'bcp ～'

とすればOKです。

プロシージャで直接ファイルから1行1行読み込むといったことはできませんが、テーブルにアップロードできさえすれば、あとはSQLでいかようにも処理できるので、工夫次第でいろんな使い方ができるでしょう。

bcpユーティリティによるテキストファイルの出力

bcpユーティリティを使ってテーブルの内容ををテキストファイルに出力するには

bcp db1.dbo.TABLE1 out test.txt -c -Usa -Psa -SSERVER1

  -c：SJIS出力
  -U：ログイン名
  -P：パスワード
  -S：SQLServerインスタンス名

こんなコマンドを実行すればいいだけです。

出力ファイルのフォーマットはデフォルトではタブ区切り形式になりますが、もしそれ以外の形式で出力したい場合は、フォーマット指定のオプションを指定するか、あるいはアップロードと同様にフォーマットファイルを指定するかすればいいのです。

それからクエリーの実行結果をテキストファイルに出力するには

bcp "SELECT * FROM TABLE1" queryout test.txt -c -Usa -Psa -SSERVER1

というようなコマンドを実行すればOKです。

テーブル名がクエリー文字列に、outパラメータがqueryoutパラメータに変わるだけです。

なお、クエリー文字列の長さが1023byteを超えると

クエリ ヒントは、 1023 バイト最大コマンド バッファサイズを超えます。

というエラーが出てしまうので注意が必要です。

クエリーがどうしても長くなる場合は、ビューを使用するなどしてクエリーが短くなるようにしましょう。

※bcpユーティリティの詳細についてはSQL Server Books Onlineなどを参照のこと。