DB に大量データ インポートしてみた SQLServer編

■ 大量データインポート
業務で色々なDBに大量データインポートをする機会があったので、各DBごとに紹介する。今回はSQLServer編。

■ bcp ユーティリティ
普通にInsert文繰り返ししてもいいんだけどbcp ユーティリティのほうが早い。今回は下記のようなテーブルに大量データインポートをしてみようと思う。

まずはこのテーブルに対して、フォーマットがどうなっているか知るために、以下のコマンドを使ってエクスポートする。パスワードを聞かれるのでパスワード入力

bcp テーブル名 out C:\sado.txt -U ユーザ名

すると以下のように質問されるので[]内のデフォルトを入力していく。

最後にフォーマット情報を保存しますか？と聞かれるのでYを入力する。これを実行すると、フォーマット情報と、そのフォーマット定義にそった形式でTableのレコードが出力される。

フォーマット定義ファイルの中身はこんな感じ

左から順番に。

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

１．データファイルのフィールド番号
２．データ型
３．プレフィックス長
４．フィールドの長さ
５．フィールドの終端文字
６．SQL Serverデータベース内でのフィールド番号
７．SQL Serverテーブルのフィールド名（実際の名前でなくてもいいが、空白であってはならない）
８．行の照合順序

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

上記フデフォルトのフォーマット定義で出力されたレコードのファイルはこんな感じ

これだとレコードごとに改行がされていないし、どれがどのカラムに対応しているのかわかりずらいので、フォーマット定義のファイルをカンマ区切りの改行有りに設定する。下記のように編集する。
最後は改行なので\r\nを付け加える。

このフォーマット定義を指定してさっきのエクスポートコマンドを打つと

bcp テーブル名 out C:\sado.txt -U ユーザ名 -f フォーマットファイル名

すると出力されたレコードがカンマくぎりの改行有りになっている。

あとは簡単。
このフォーマット定義にしたがってInsertしたい入力ファイルを作成し、以下のコマンドを打つ

bcp テーブル名 in インポート入力ファイル -U ユーザ名 -f フォーマットファイル名

すると、大量レコードでも時間がかからずに挿入される。
今回10万レコードで試したが10秒かかりませんでした。（環境によってはもうちょっと時間かかるかも）