SQL Server - BULK INSERT

概要

BULK INSERT文は、SQL Serverで大量のデータを高速にテーブルに取り込むための機能である。
通常のINSERT文と比較して、効率的にデータを取り込むことができる。

 -- 使用例
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
 );

重要な特徴として、BULK INSERT文はトランザクションログへの書き込みを最小限に抑えることで高速な処理を実現している。
これは、大量データの取り込み時に大きな効果を発揮する。

データソースとしては、カンマ区切り (CSV) や タブ区切り (TSV) 等の形式のテキストファイルが使用できる。
FIELDTERMINATORオプションは列の区切り文字、ROWTERMINATORオプションは行の区切り文字を指定する。

パフォーマンスに影響を与える重要な要素として、以下に示すような設定がある。

 WITH
 (
    BATCHSIZE = 1000,
    TABLOCK,
    CHECK_CONSTRAINTS = OFF,
    FIRE_TRIGGERS = OFF
 )

これらのオプションを適切に設定することにより、パフォーマンスを向上させることができる。
特に、TABLOCKオプションは、テーブルレベルでのロックを取得することにより、行レベルのロックによるオーバーヘッドを避けることができる。

※注意
BULK INSERT文を使用する場合は、ファイルに対する適切なアクセス権限が必要となる。
また、データファイルの形式とテーブルのスキーマが正確に一致している必要がある。

エラーハンドリングについては、ERRORFILEオプションを使用することにより、取り込みに失敗した行を別ファイルに出力して後から確認することができる。

 WITH
 (
    ERRORFILE = 'C:\Errors\error.txt',
    MAXERRORS = 10
 )

BULK INSERT文は、大量データの取り込みに非常に効果的なツールであるが、適切な設定と事前準備が重要となる。

BULK INSERTの例

下記は、CSVファイル(カンマ区切り)をインポートする際の例である。

• 文字コードがShift-JISの場合

 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'char',
                 FIELDTERMINATOR = ','
         );
 GO

• 文字コードがUNICODEの場合 (SQL Server 2014 R2以前)

  ただし、SQL Server 2014 R2以前では、下記のクエリを実行しても UTF-8形式のファイルは取り込めない。

  (UTF-8固有の文字が無いUTF-8形式のファイルはインポートできる)

 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'widechar',
                 FIELDTERMINATOR = ','
         );
 GO

• 文字コードがUTF-8の場合 (SQL Server 2014 R2以降)

  SQL Server 2014 R2以降では、UTF-8形式のファイルをインポートすることが出来る。

 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'char',
                 CODEPAGE        = '65001',
                 FIELDTERMINATOR = ','
         );
 GO

エラーハンドリング

基本的なエラーハンドリングの設定を以下に示す。

 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    ERRORFILE = 'C:\Errors\error.txt',
    MAXERRORS = 10,
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
 );

• ERRORFILEオプション

  エラーが発生した行の詳細情報を指定されたファイルに出力する。

  このファイルには、エラーの発生した行番号、エラーの種類、具体的なエラーメッセージ等が記録される。

  
  

• MAXERRORSオプション (重要)

  許容するエラーの最大数を指定する。

  この数を超えるとBULK INSERT処理全体が中止される。

  例えば、MAXERRORSオプションを10に設定した場合、11個目のエラーが発生した時点で処理が停止する。

  
  

• CHECK_CONSTRAINTSオプション

  データの検証に関するエラーハンドリングの設定である。

  このオプションをONに設定する場合、制約違反のチェックが行われる。

  ただし、パフォーマンスへの影響があるため、データの信頼性が確保されている場合はOFFにする。

  
  

• KEEPNULLSオプション

  空の文字列をNULLとして扱うかどうかを制御する。

  これにより、NULL制約に関連するエラーを適切に処理することができる。

また、詳細なエラー情報を取得する場合は、TRY-CATCHブロックを使用する。

 BEGIN TRY
    BULK INSERT TargetTable
    FROM 'C:\Data\source.txt'
    WITH
    (
       ERRORFILE = 'C:\Errors\error.txt',
       MAXERRORS = 10
    );
 END TRY
 BEGIN CATCH
    SELECT 
       ERROR_NUMBER() AS ErrorNumber,
       ERROR_MESSAGE() AS ErrorMessage,
       ERROR_SEVERITY() AS ErrorSeverity,
       ERROR_STATE() AS ErrorState;
 END CATCH

実務では、エラーファイルの内容を定期的に監視して、必要に応じて修正アクションを取ることが推奨される。
エラーファイルには、以下に示すような情報が含まれる。

• エラーが発生した行番号
• エラーの発生した列
• エラーの種類 (データ型の不一致、制約違反等)
• 具体的なエラーメッセージ

エラー発生時のロールバック動作についても理解が重要である。
BATCHSIZEオプションを使用している場合、エラーが発生したバッチのみがロールバックされて、それ以前に正常に取り込まれたデータは保持される。