SQLServerに100万件データをブチ込むBCPコマンドについて

6116 ワード
SQLServer BCP SQLServer テキストリンク

BCPコマンドとは？

BCPコマンドによって以下のようなことができます。
・テーブル内のデータをファイルに一括エクスポートする
・予め作成したファイルのデータをテーブルに一括インポートする

テーブルにデータを大量登録する機会があったため利用しました。

ちなみに、BCPは「Bulk Copy Program」の略称。

【参考】：bcp ユーティリティ Microsoft公式

SQL文じゃダメ？BCPを使う利点

個人的には以下がイケてるなーと思っています

・大量データを(楽に)扱える
・DBとのインポート/エクスポートが一つのコマンドを覚えるだけで実現できる

BULK INSERT文で一括インポートしたり、 SSMSのSELECT結果をSave Result as...して一括エクスポートしたりはできますが、一つのコマンドラインとしてまとまってるのはありがたいなと思いました。覚えることが少なく済むので。

BCP以外にも、経験上こうやってエクスポート/インポートしてたよとかあれば、ぜひ教えてください。

参考
https://qiita.com/fuk101/items/d98716a48d69d5c7f1a7

主な使い方（大量データインポート）

コマンド例
※テーブル1つにつき1コマンドずつ叩く

bcp {テーブル名} in {データ登録ファイル(txt)}
-c -S {DBServer名} -d {Database名} -U l{User名}-P {パスワード} -q -E

オプション一覧（有志のブログ）
https://mr-star.hatenablog.com/entry/sqlserver/020

挿入元のファイル形式
タブ切り分けファイル。Excelのフィル機能で大量データ作って、かつ"エクスポート"機能を使うと気軽にデータ作りこめます
"ファイル" > 左リボンから"エクスポート" > "ファイルの種類の変更" > "テキスト（タブ区切り）*.txt"

コピーに成功した時のログ
100万件突っ込むのに30分ぐらいかかりました。

コピーを開始しています...
SQLState = 22005, NativeError = 0
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
...
1000 rows sent to SQL Server. Total sent: 998000
1000 rows sent to SQL Server. Total sent: 999000
999997 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1423593 平均 : (702.45 行/秒)

エラーあるある

このサイトがめっちゃ分かりやすい。4つ上がっているが、本当にあるあるofあるあるでした。
http://comfair2.blog24.fc2.com/blog-entry-143.html?sp

↓テーブルのフィールド長よりもインポートデータのほうが長い場合。

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

↓int型の項目に日付型をセットしようとした場合など

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

↓テーブルとファイル間で項目数が異なる場合など

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered inBCP data-file

↓DB固有の制約に違反した場合（これは非NULLカラムにNULLを入れたとき）

SQLState = 23000, NativeError = 515
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'id', table 'dbo.user'; column does not allow nulls. INSERT fails.

非NULL違反の原因が、Excelシートの仕様(?)で勝手に空文字が入ってるのが原因な時がある。ctrl + End押して、セルが意図したデータの終わりに飛ぶか確認してみてください。

主な使い方（データエクスポート）

あんまり使わなかったのでちょっと熱意薄いですが、DBからファイルにエクスポートするときのコマンドも紹介します。
インポート時の「in」を「out」にするだけで使えます。

コマンド例

bcp {テーブル名} out {データ登録ファイル(txt)}
-c -S {DBServer名} -d {Database名} -U l{User名}-P {パスワード} -q -E

ちょっと気に要らんところ...

・空文字orNULLをスキップできるようにしてほしい