ストアドでテキストファイルの読込み

質問

SV2005のC#にて固定長のテキストファイルを文字数で分解し
SQL Server 2005 のテーブルとマスタ有無チェックを行い
データグリッドビューに表示しているのですが
データ件数が万単位でありかなりの時間が掛かります

その為、対象ファイルをデータベースサーバーに複写して
ストアドでワークテーブルにでも分解しSELECTをしようとして
色々調べているのですが…

まず SELECT * OPENROWSET( BULK 'ファイル名' … を使ってSELECTを試みたのですが
単純にSELECTすると先頭の1行のみしか取れない事を発見。

BULK INSERT で行えば複数行可能のようなのですが
SQLなのでファイル名を変数として渡せないし
ワークテーブルに書き込む時に項目分解を行いたいので今一。

sp_OACreateでFSO経由で読み込むしかないかと思ったのですが
簡単な方法をご存知の方が居たらと思い投稿させて頂きました

回答No.2

jamshid6

補足ですが、
・ヘッダーもトレーラーもフォーマットファイルで落とすのが難しければ、ワークに入れて読まなければよいです。
・カーソルで回すと遅くなります。せっかくDBに入れるなら、必要な処理はまとめてクエリで行った方がいいです。
Transact-SQLの場合、１行に一回ストアドやテーブル関数をコールするとか、そういう処理が含まれない限り、私はほとんどカーソルを使うことはありません。

お礼

度々の回答有難うございます。

> ・カーソルで回すと遅くなります
確かに。

> クエリで行った方がいいです
同感です。
私も極力カーソルは使わない主義です。

現在
http://blogs.wankuma.com/esten/archive/2007/05/14/76724.aspx
http://blogs.wankuma.com/esten/archive/2007/05/17/77081.aspx
この辺りを参考にさせていただいてカーソルを使わない方法を模索中

ただ、フォーマットにより無理なもののみループかなぁっと思っております。

回答No.1

jamshid6

諦めるの早いんですね。。

>単純にSELECTすると先頭の1行のみしか取れない事を発見。
これは単にファイル全体を１行で取り込んでしまったということでしょうね。
画像なんかをテーブルに取り込むときなどにはよく使いますから。

>BULK INSERT で行えば複数行可能のようなのですが
>SQLなのでファイル名を変数として渡せないし
>ワークテーブルに書き込む時に項目分解を行いたいので今一。
・・と書かれた問題をクリアする例を示しますので、使えそうなら調べてみてください。

DECLARE @sql varchar(max)
DECLARE @FILENAME varchar(100)
DECLARE @FMTFILENAME varchar(100)
SET @FILENAME='C:\hogehoge\data.txt'
SET @FMTFILENAME='C:\hogehoge\fmtfile.xml'

CREATE TABLE #WK ([F1][varchar](5),[F2][varchar](5),[F3][varchar](10))

SET @sql='BULK INSERT #WK FROM '''+@FILENAME+''''
+' WITH (FORMATFILE='''+@FMTFILENAME+''')'
EXEC (@sql)

SELECT * FROM #WK
DROP TABLE #WK

フォーマットファイルの作り方は以下を参照。固定長でも問題ありません。
http://msdn.microsoft.com/ja-jp/library/ms191516(SQL.90).aspx

お礼

ご回答、有難うございます。

投稿後、文字列で組み立てたSQL実行のEXECを思い出し BULK INSERT を試してOKたったのですが…

データもヘッダー、トレーラーがあってFORMATも使えずにいます。

>> 単純にSELECTすると先頭の1行のみしか取れない事を発見。
> これは単にファイル全体を１行で取り込んでしまったということでしょうね。
> 画像なんかをテーブルに取り込むときなどにはよく使いますから。
お！『ファイル全体を１行で』だったのか
再確認するとそのようです。

回答のワークに取り込んでの例をみて
FSO使うより全体をワークに取り込んでカーソルループを使うほうが便利かも　と思いこれから、ストアド作成に励みます。

レイアウトパターンが５種類以上あるのでデータの確認のほうが大変なのですが