【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秒で終わりました。

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