2008年5月17日土曜日

【SQL SERVER】固定長データのインポート

むしろ、なんで固定長なん・・・。

それはさておき、メモと。

ほとんどのことがMSDNに書かれてることと同じです。

固定長のデータを使うには、xmlファイルを作成しないと

いけないらしいので、bcpコマンドを使ってある程度まで作成。

その後にちょこっと編集。編集しなくてもできるのかなぁ・・・。

まずはコマプロで以下のように入力。

bcp tempdb..test format nul -x -f C:\test.xml -c -S server\tempdb -U sa -P sa

出来上がったxmlファイルを編集。
  1. xsi:type="CharTerm"を"CharFixed"に変換。
  2. TERMINATOR="\t"、TERMINATOR="\r\n"を削除。
  3. MAX_LENGTHをLENGTHに変換。
  4. 最終行に<FIELD ID="最終行" xsi:type="CharTerm" TERMINATOR="\r\n"/>を追加。
  5. decimal型などのデータ長を微調整。
  6. テーブル列をスキップする場合は<FIELD>と<COLUMN>行を削除。
  7. 上記にともない対応しているIDとSOURCEの番号のズレを合わせる。
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://test/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="10"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="6"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="C1" xsi:type="SQLINT" />
<COLUMN SOURCE="2" NAME="C3" xsi:type="SQLINT" />
</ROW>
</BCPFORMAT>

ここまで、出来上がったら後は、OPENROWSET(BULK ...)を利用して

いっきに、インサートする為にTransact-SQLを作成。

USE tempdb;
GO
INSERT INTO test
(col1,col3)
SELECT col1,col3
FROM OPENROWSET
(
BULK 'C:\test.txt',
FORMATFILE='C:\test.xml',
FIRSTROW='3' --3行目からスタート
 ) AS tbl;
GO
SELECT count(*) FROM tbl;

上記では、testテーブルの列col2をスキップし、

さらに入力ファイルtest.txtの3行目から読み込む。


後は実行するのみ。

コマプロからsqlcmdで上記のsqlファイルを実行する。

sqlcmd -S server\temdb -U sa -P sa -i C:\test.sql -o C:\count.txt

上記を利用して250万件のレコード(約230MB)が40秒で終わりました。

但し、環境は個人では到底手が出ないハイスペックマシンです^^;

0 件のコメント: