ぱそくま ろご
Windows > SQL Server > BULK INSERT 基礎

BULK INSERT 基礎

BULK INSERTとは

CSVファイルやTAB区切りファイルを一括でテーブルに挿入できます。 なお、処理も高速で数千件や数万件のデータファイルを取り込む時などに非常に便利です。

まずはBULK INSERTの準備

BULK INSERTを行うユーザーは「bulkadmin」権限が必要です。 (もちろんsaは「bulkadmin」を付与せずにBULK INSERTを実行できますが、saでの実行はお勧めしません。) ユーザーへの権限の付与方法は以下の通りです。

例:ユーザー「bulkuser」に「bulkadmin」権限を付与する

EXEC sp_addsrvrolemember 'bulkuser', 'bulkadmin';

BULK INSERTの例

例えば、DBサーバーに下表のようなテーブルとCSVファイルがあったとします。

テーブル定義とCSVファイルの中身
テーブル定義
/**
 * MEMBER_INFOテーブル
 * [フィールド]
 * ・メールアドレス
 * ・名前(姓)
 * ・名前(名)
 */
CREATE TABLE dbo.MEMBER_INFO
(
 MAIL_ADDR VARCHAR(50) NOT NULL,
 NAME_MEI VARCHAR(50) NOT NULL, 
 NAME_SEI VARCHAR(50) NOT NULL, 
 CONSTRAINT PK_MEMBER_INFO PRIMARY KEY CLUSTERED (MAIL_ADDR)
) ON [PRIMARY]
CSVファイル
メールアドレス,名前(姓),名前(名)
tarou@example.com,てすと,太郎
hanako@example.com,てすと,花子
jirou@example.com,てすと,次郎
.
.


このCSVファイルをBULK INSERTを使ってdbo.MEMBER_INFOにインポートするには以下のSQLを実行します。

/**
 *
 * pasodbデータベースにあるMEMBER_INFOテーブルに
 * d:\data\member.csvをbulk insertする方法
 *
 */
BULK INSERT pasodb.dbo.MEMBER_INFO
FROM 'D:\data\member.csv'
WITH
(
 FIRSTROW = 2,
 FIELDTERMINATOR  = ',',
 CHECK_CONSTRAINTS
)

なお、上記BULK INSERT文を解説すると

  • 「BULK INSERT pasodb.dbo.MEMBER_INFO」→挿入先テーブルは「pasodb.dbo.MEMBER_INFO」とする。
  • 「FROM 'D:\data\member.csv'」→インポートするファイルは「D:\data\member.csv」とする。
  • 「FIRSTROW = 2」→2行目からインポートする(タイトル行を読まない)
  • 「FIELDTERMINATOR = ','」→インポートするファイルの区切り文字はカンマ(カンマ区切りCSV)
  • 「CHECK_CONSTRAINTS」→制約のチェックを行う。

となります。その他のWITHオプションは下表を参考にしてください。

WITH オプションの一覧表

BULK INSERTではインサートする時の条件をWITHオプションで指定します。指定できるオプションは以下の通りです。

オプション名 引数の型 引数初期値 説明
BATCHSIZE 数値 なし 一括インポートの場合、全てが1つのトランザクションとなります。よって、失敗した場合全てがロールバックされます。これを指定すると、そのサイズ毎にトランザクションになりますので、途中、失敗しても、それまでは処理されます。関連:KILOBYTES_PER_BATCH、ROWS_PER_BATCH
CHECK_CONSTRAINTS なし - CHECK制約やFOREIGN KEY制約をチェックする場合指定します。よって、意図的に指定しないとチェックされませんのでご注意を。
CODEPAGE 文字列 'OEM' データファイルの文字コードを'ACP'(SJIS)、'OEM'(システムの既定の文字コード)、'RAW'、'code_page'から選択します。'code_page'は任意のコードページを指定します。なぜかUTF-8はサポートしていませんのでご注意を。もちろん、データファイルだけの話です。
DATAFILETYPE 文字列 'char' INSERT処理のデータ型を'char'(文字形式)、'native'(ネイティブデータ型)、'widechar'(Unicode文字)、'widenative'(ネイティブデータ型)から選択します。
FIELDTERMINATOR 文字列 '\t' データの区切り文字を指定します。','を規定値にしてほしい、今日この頃。関連:ROWTERMINATOR。
FIRSTROW 数値 1 読み込み開始行を指定します。CSVファイル等最初にタイトル行がある場合は2を指定しましょう。
FIRE_TRIGGERS なし - これを指定すると挿入先のテーブルのトリガーをを実行します。これを指定しないと、実行されないのでご注意を。
FORMATFILE 文字列 - フォーマットファイルのパスをしてします。
KEEPIDENTITY なし - インポートしたデータファイルのID値をID列に使用することに指定します。
KEEPNULLS なし - 空の列が挿入される場合、NULLとして扱われます。
KILOBYTES_PER_BATCH 数値 なし バッチ処理1回当たりの概算キロバイト数を指定します。
LASTROW 数値 0 読み込み最終行を指定します。0の場合は最後まで読み込みます。一番最後の行が合計値などの場合、読み込みこみたくない時に指定します。ただし、行数を数えるのは大変ですね。
MAXERRORS 数値 10 許容されるエラーの数を指定します。
ORDER 文字列 なし 並べ替えをするカラムを指定します。例:FIRST_NAME, MAIL_ADDR DESC
ROWS_PER_BATCH 数値 なし データ ファイル内にあるデータ行の概算数を指定します。
ROWTERMINATOR 文字列 '\r\n' 行の区切り文字を指定します。大抵はこのままでOK?
TABLOCK なし - テーブルロックを取得します。なお、指定しない場合は行ロックとなります。
ERRORFILE 文字列 なし エラーをファイルに出力する場合、そのファイルを指定します。