SQL Server で BULK INSERT する (1) 基本編
クロスワープの大鷲です。
データ移行の時や、テスト データを大量に作成したいような時には、SQL Server の BULK INSERT を使うのが便利です。
しかし、ちょっと間違えると非常に不親切なエラーメッセージを出して失敗するので、使い方がよくわからないという難しさがあります。
そこで今回は、BULK INSERT の使い方について簡単に説明します。
なお、今回は事前にテキスト形式で作成してあるデータを SQL Server に取り込むというシナリオにします。
基本的な使い方
今回は、以下のようなテーブルにデータを INSERT することにします。
| 列名 | 型 |
|---|---|
| Id | int |
| Name | nvarchar(100) |
| Age | int |
DDL は至ってシンプルです。
CREATE TABLE [dbo].[People] ( [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [Name] [nvarchar](100) NOT NULL, [Age] [int] NOT NULL );
これに対して BULK INSERT するための DML は、本当に最小限のものでは以下のようなものになります。
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk1.txt' WITH ( DATAFILETYPE = 'widechar' );
これで取り込めるファイル（D:\Bulk\Bulk1.txt）は、以下のようなものになります。
0 アリス 20 0 ボブ 31 0 チャーリー 56
タブ（\t）区切りで、UTF-16（LE でも BE でも可だが BOM が必要）で保存されています。
改行コードは CR LF（\r\n）です。
先頭の 0 は Id 列に相当しますが、SQL Server 側で自動採番（IDENTITY）になっていますので、ファイル中の値はダミーです。
以上が最も簡単な使い方です。
そして、出来る限り、この使い方に寄せていくのが良いと思います。
取り込むファイルの方を、出来る限り、この形式に近付けるべきです。
列区切り文字を変える
上記のデータファイルは、列の区切り文字がタブでした。
しかし、取り込むデータの中にタブが入っている場合、列区切り文字を変えなければなりません。
例えば、バーティカル バー（'|'）を使うのであれば、以下のようになります。*1
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk2.txt' WITH ( DATAFILETYPE = 'widechar', FIELDTERMINATOR = '|' );
データ ファイル（D:\Bulk\Bulk2.txt）は、以下のようなものになります。
0|アリス|20 0|ボブ|31 0|チャーリー|56 0|ここにタブ→ ←ここにタブ|41
行区切り文字を変える
取り込みたいデータの中に改行文字（CR LF）がありますか。*2
仕方ありません。行区切り文字を変えましょう。
列区切り文字はタブとし、行区切り文字をバーティカル バー（'|'）にします。
DML はこうです。
BULK INSERT [dbo].[People] FROM 'D:\Bulk\Bulk3.txt' WITH ( DATAFILETYPE = 'widechar', ROWTERMINATOR = '|' );
データ ファイルはこちら。
0 アリス 20|0 ボブ 31|0 チャーリー 56|0 ここに改行→ ←ここに改行 71|
非常にわかりづらいですが、テーブルには 4 行が INSERT されます。
4 行目の Name は
ここに改行→ ←ここに改行
になります。
もし、「元ファイルが Linux 環境で作られたものだから改行文字が LF になっている」といった理由で行区切り文字の変更を考えているのであれば、ファイルをテキスト エディターで開いて改行コードを変換した方がいいでしょう。
以上
これだけ知っていれば大抵のケースには対応できると思います。
何度でも言いますが、これよりも複雑なケースでは、データ ファイルの方を修正して対応すべきです。
冒頭にも書きましたが、SQL の BULK INSERT は、データ移行やテスト データの大量投入などに便利です。
つまり、必要な頻度はさほど多くないということです。*3
テキスト エディターで元ファイルを修正するのも、さほど大変ではないでしょう。*4
それよりも、データ ファイルをそのまま使おうとして、BULK INSERT と格闘するほうが、ずっと不毛です。
しかし、もう少し複雑なケースにも対応する機能はありますので、蛇足ではありますが、次回以降に紹介したいと思います。
最後に、注意点を一つ。
CSV を取り込むのは諦めてください。*5
