SQL Server - BULK INSERT

提供：MochiuWiki : SUSE, EC, PCB
ナビゲーションに移動 検索に移動

概要

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文は、データの挿入のみを行うコマンドで、テーブルの作成機能は持っていない。
データを挿入する場合は、列の定義や制約などのテーブル構造が必要となる。

 CREATE TABLE customers (
    id    INT,
    name  VARCHAR(100),
    email VARCHAR(200)
);



使用例

カンマ区切りファイルのインポート

  • FIRSTROWオプションでヘッダ行をスキップする。
  • MAXERRORSオプションでエラー許容数を設定する。
  • エラーログファイルを指定する。 (問題追跡が可能)


# CSVファイルの内容
John,Doe,30,New York
Jane,Smith,25,Los Angeles



 BULK INSERT Employees
 FROM 'D:\Data\employees.csv'
 WITH
 (
    FIRSTROW = 2,                     /* ヘッダ行をスキップ */
    FIELDTERMINATOR = ',',            /* フィールド区切り文字 */
    ROWTERMINATOR = '\n',             /* 行区切り文字 */
    MAXERRORS = 2,                    /* 許容するエラーの最大数 */
    ERRORFILE = 'D:\Data\errors.txt'  /* エラーログファイル */
 );


タブ区切りファイルのインポート (日付フォーマット指定)

  • DATE_FORMATで日付形式を明示的に指定する。
  • FORMATオプションでCSV形式を指定する。


# TSVファイルの内容
OrderID    OrderDate    CustomerID    Amount
1001    2024-01-01    CUST001    1500.50



 BULK INSERT Orders
 FROM 'D:\Data\orders.txt'
 WITH
 (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '\t',     /* タブ区切り */
    ROWTERMINATOR = '\n',
    FORMAT = 'CSV',             /* CSVフォーマットを使用 */
    DATE_FORMAT = 'yyyy-MM-dd'  /* 日付フォーマットを指定 */
 );


固定長フォーマットファイルのインポート

  • フォーマットファイルを使用して列幅を定義する。
  • KEEPNULLSオプションでNULL値の処理を制御する。


# データ例 (各フィールドが固定長)
ID  NAME      DEPT
001 JOHN      SALES
002 MARY      IT



 BULK INSERT Employees
 FROM 'D:\Data\employees_fixed.txt'
 WITH
 (
    DATAFILETYPE = 'char',
    FORMATFILE = 'D:\Data\format.fmt',  /* フォーマットファイルを指定 */
    CODEPAGE = '65001',                 /* UTF-8エンコーディング */
    KEEPNULLS                           /* NULL値を保持 */
 );


XMLフォーマットファイルを使用したインポート

  • 詳細なデータマッピングが可能になる。
  • CHECK_CONSTRAINTSオプションで制約チェックを有効化する。
  • FIRE_TRIGGERSオプションでトリガー処理を制御する。


 /* より詳細なデータマッピングが必要な場合 */
 
 BULK INSERT Products
 FROM 'D:\Data\products.txt'
 WITH
 (
    FORMATFILE = 'D:\Data\products_format.xml',
    CHECK_CONSTRAINTS,                           /* 制約チェックを有効化 */
    FIRE_TRIGGERS,                               /* トリガーを発火 */
    KEEPIDENTITY                                 /* ID列の値を保持 */
 );


バッチ処理によるBULK INSERT

  • BATCHSIZEオプションとROWS_PER_BATCHオプションでパフォーマンス最適化する。
  • TABLOCKオプションでロック戦略を制御する。
  • ORDER句でデータの順序を指定する。


 /* 大量データを分割してインポートする場合 */
 
 BULK INSERT LargeTable
 FROM 'D:\Data\large_data.csv'
 WITH
 (
    BATCHSIZE = 1000,          /* バッチサイズを指定 */
    ROWS_PER_BATCH = 10000,    /* バッチあたりの行数 */
    TABLOCK,                   /* テーブルロックを使用 */
    ORDER (OrderColumn ASC)    /* データの順序を指定 */
 );


文字コードに関する使用例

以下の例では、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文は、データの挿入のみを行うコマンドで、テーブルの作成機能は持っていない。
データを挿入する場合は、列の定義や制約などのテーブル構造が必要となる。

既存のテーブル構造が不明な場合は、データファイルの内容を確認して、適切な列の型とサイズを決定してからテーブルを作成する。

また、データファイルの内容を分析してテーブルを作成するプロセスをSQLで自動化することができる。

以下の例では、まず、CSVファイルを一時テーブルに読み込み、ヘッダ行とサンプルデータを分析してデータ型を推測している。
次に、各データ型である数値型、日付型、文字列型 (長さに応じて適切なサイズを設定) を推測して、CREATE TABLE文を使用して動的に生成して実行している。
最後に、作成されたテーブル構造を確認している。

※注意

  • データ型の推測は最初の行のみを基に行われるため、完全な精度は保証されない。
  • 大規模なデータセットの場合、より多くのサンプルを分析することを推奨する。
  • 特殊な要件 (一意制約、外部キー等) は手動で追加する必要がある。


このSQLを実行した後、必要に応じてALTER TABLE文を使用して列定義を調整できる。

 /* 一時テーブルを作成してCSVファイルを読み込む */
 CREATE TABLE #TempRawData (
    LineContent NVARCHAR(MAX)
 );
 
 /* CSVファイルを一時テーブルに読み込む */
 BULK INSERT #TempRawData
 FROM 'C:\data\sample.csv'
 WITH (
    ROWTERMINATOR = '\n'
 );
 
 /* ヘッダ行を取得 (1行目) */
 DECLARE @HeaderLine NVARCHAR(MAX);
 SELECT TOP 1 @HeaderLine = LineContent FROM #TempRawData;
 
 /* データの1行目を取得 (実際のデータ形式を確認するため) */
 DECLARE @SampleDataLine NVARCHAR(MAX);
 SELECT TOP 1 @SampleDataLine = LineContent
 FROM #TempRawData 
 WHERE LineContent != @HeaderLine;
 
 /* 動的SQLを生成するための変数を宣言 */
 DECLARE @CreateTableSQL NVARCHAR(MAX);
 DECLARE @ColumnNames TABLE (
    ColumnName NVARCHAR(255),
    SampleValue NVARCHAR(MAX)
 );
 
 /* ヘッダーとサンプルデータを分割して@ColumnNamesに格納 */
 ;WITH SplitHeaders AS (
    SELECT value AS ColumnName, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM STRING_SPLIT(@HeaderLine, ',')
 ),
 SplitData AS (
    SELECT value AS SampleValue, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM STRING_SPLIT(@SampleDataLine, ',')
 )
 INSERT INTO @ColumnNames
 SELECT h.ColumnName,
        d.SampleValue
 FROM SplitHeaders h
 JOIN SplitData d ON h.RowNum = d.RowNum;
 
 /* CREATE TABLE文を動的に生成 */
 SET @CreateTableSQL = N'CREATE TABLE ImportedData (';
 
 SELECT @CreateTableSQL = @CreateTableSQL + 
    QUOTENAME(ColumnName) + ' ' +
    CASE 
       WHEN ISNUMERIC(SampleValue) = 1 AND CHARINDEX('.', SampleValue) > 0 
          THEN 'DECIMAL(18,2)'
       WHEN ISNUMERIC(SampleValue) = 1 
          THEN 'INT'
       WHEN ISDATE(SampleValue) = 1 
          THEN 'DATETIME'
       WHEN LEN(SampleValue) <= 50 
          THEN 'NVARCHAR(50)'
       WHEN LEN(SampleValue) <= 255 
          THEN 'NVARCHAR(255)'
       ELSE 'NVARCHAR(MAX)'
    END + ',' + CHAR(13)
 FROM @ColumnNames;
 
 /* 最後のカンマを削除して閉じカッコを追加 */
 SET @CreateTableSQL = LEFT(@CreateTableSQL, LEN(@CreateTableSQL) - 2) + ')';
 
 /* テーブルが既に存在する場合は削除 */
 IF OBJECT_ID('ImportedData', 'U') IS NOT NULL DROP TABLE ImportedData;
 
 /* 新しいテーブルを作成 */
 EXEC sp_executesql @CreateTableSQL;
 
 /* 一時テーブルをクリーンアップ */
 DROP TABLE #TempRawData;
 
 /* 作成されたテーブル定義を確認 */
 SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale
 FROM sys.columns c
 JOIN sys.types t ON c.user_type_id = t.user_type_id
 WHERE object_id = OBJECT_ID('ImportedData');



エラーハンドリング

 /* 基本的なエラーハンドリングの設定 */
 
 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ブロックを使用する。

 /* BULK INSERTの基本的なエラーハンドリング設定 */
 /* ERRORFILEにエラー内容を出力し、最大10件のエラーまで許容 */
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    /* エラー情報を出力するファイルパスを指定 */
    ERRORFILE = 'C:\Errors\error.txt',
    /* 許容するエラーの最大数。この数を超えると処理が中止される */
    MAXERRORS = 10,
    /* ヘッダ行をスキップし、2行目からデータとして取り込む */
    FIRSTROW = 2,
    /* 列の区切り文字をカンマに指定 */
    FIELDTERMINATOR = ',',
    /* 行の区切り文字を改行に指定 */
    ROWTERMINATOR = '\n'
 );
 
 /* TRY-CATCHブロックを使用したより詳細なエラーハンドリング */
 BEGIN TRY
    BULK INSERT TargetTable
    FROM 'C:\Data\source.txt'
    WITH
    (
       /* エラー情報の出力先を指定 */
       ERRORFILE = 'C:\Errors\error.txt',
       /* 10件までのエラーを許容 */
       MAXERRORS = 10
    );
 END TRY
 BEGIN CATCH
    /* エラー発生時に詳細情報を取得 */
    SELECT 
       /* エラー番号を取得 */
       ERROR_NUMBER() AS ErrorNumber,
       /* エラーメッセージの詳細を取得 */
       ERROR_MESSAGE() AS ErrorMessage,
       /* エラーの重要度を取得 (0-25の値) */
       ERROR_SEVERITY() AS ErrorSeverity,
       /* エラーの状態コードを取得 */
       ERROR_STATE() AS ErrorState;
 END CATCH


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

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


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

 /* ターゲットテーブルに対するBULK INSERT設定 (NULL値とバッチ処理の制御) */
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    /* 空の文字列をNULLとして扱うのではなく、空文字として保持 */
    /* このオプションを指定しない場合、空文字列は自動的にNULLに変換される */
    KEEPNULLS,
 
    /* 一度に処理する行数を5000行に設定 */
    /* メモリ使用量とパフォーマンスのバランスを取るために使用 */
    /* 大きすぎる値はメモリ消費が増加し、小さすぎる値は処理時間が増加 */
    BATCHSIZE = 5000,
 
    /* テーブルの制約チェックを有効化 */
    /* 外部キー制約、CHECK制約、一意性制約などのすべての制約を確認 */
    /* パフォーマンスは低下するが、データの整合性が保証される */
    CHECK_CONSTRAINTS,
 
    /* エラー発生時の情報を出力するファイルを指定 */
    ERRORFILE = 'C:\Errors\error.txt',
 
    /* 最大エラー数を設定 (この数を超えると処理が中止) */
    MAXERRORS = 10,
 
    /* データファイルの区切り文字設定 */
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
 
    /* ヘッダ行をスキップ */
    FIRSTROW = 2
 )
 
 /* エラーハンドリングのためのTRY-CATCHブロック */
 BEGIN TRY
    /* 上記のBULK INSERT文を実行 */
    /* 注意: EXEC内のBULK INSERT文は、上記のWITHブロックと同じ設定を使用 */
    EXEC('
       BULK INSERT TargetTable
       FROM ''C:\Data\source.txt''
       WITH
       (
          KEEPNULLS,
          BATCHSIZE = 5000,
          CHECK_CONSTRAINTS,
          ERRORFILE = ''C:\Errors\error.txt'',
          MAXERRORS = 10,
          FIELDTERMINATOR = '','',
          ROWTERMINATOR = ''\n'',
          FIRSTROW = 2
       )
    ');
 
    /* 正常終了時のログ出力 */
    PRINT 'BULK INSERT successfully completed';
 END TRY
 BEGIN CATCH
    /* エラー情報の取得と出力 */
    SELECT 
       GETDATE() AS ErrorTime,             /* エラー発生時刻 */
       ERROR_NUMBER() AS ErrorNumber,      /* エラー番号 */
       ERROR_SEVERITY() AS ErrorSeverity,  /* エラーの重要度 */
       ERROR_STATE() AS ErrorState,        /* エラーの状態 */
       ERROR_PROCEDURE() AS ErrorProc,     /* エラーが発生したプロシージャ */
       ERROR_LINE() AS ErrorLine,          /* エラーが発生した行番号 */
       ERROR_MESSAGE() AS ErrorMessage;    /* エラーメッセージの詳細 */

    /* エラーのログテーブルへの挿入 (オプション) */
    INSERT INTO ErrorLog (
       ErrorTime, 
       ErrorNumber, 
       ErrorMessage
    )
    VALUES (
       GETDATE(),
       ERROR_NUMBER(),
       ERROR_MESSAGE()
    );
 END CATCH;


ただし、実務では、以下に示すように動的なパラメータを使用することが一般的である。
変数を使用することにより柔軟な運用が可能になり、また、パラメータ化することで再利用性も高まる。

 /* 動的パラメータの使用 */
 
 DECLARE 
    @FilePath NVARCHAR(500)  = 'C:\Data\source.txt',
    @ErrorPath NVARCHAR(500) = 'C:\Errors\error.txt',
    @BatchSize INT = 5000,
    @MaxErrors INT = 10,
    @BulkCmd NVARCHAR(MAX);
 
 /* 動的なBULK INSERT文を構築 */
 SET @BulkCmd = '
    BULK INSERT TargetTable
    FROM ''' + @FilePath + '''
    WITH
    (
       KEEPNULLS,
       BATCHSIZE       = ' + CAST(@BatchSize AS NVARCHAR(10)) + ',
       CHECK_CONSTRAINTS,
       ERRORFILE       = ''' + @ErrorPath + ''',
       MAXERRORS       = ' + CAST(@MaxErrors AS NVARCHAR(10)) + ',
       FIELDTERMINATOR = '','',
       ROWTERMINATOR   = ''\n'',
       FIRSTROW        = 2
    )';
 
 BEGIN TRY
    /* 構築したBULK INSERT文を実行 */
    EXEC(@BulkCmd);
 
    /* 正常終了時のログ出力 */
    PRINT 'BULK INSERT successfully completed';
 END TRY
 BEGIN CATCH
    /* エラー情報の取得と出力 */
    SELECT 
       GETDATE()         AS ErrorTime,      /* エラー発生時刻 */
       ERROR_NUMBER()    AS ErrorNumber,    /* エラー番号 */
       ERROR_SEVERITY()  AS ErrorSeverity,  /* エラーの重要度 */
       ERROR_STATE()     AS ErrorState,     /* エラーの状態 */
       ERROR_PROCEDURE() AS ErrorProc,      /* エラーが発生したプロシージャ */
       ERROR_LINE()      AS ErrorLine,      /* エラーが発生した行番号 */
       ERROR_MESSAGE()   AS ErrorMessage;   /* エラーメッセージの詳細 */

    /* エラーのログテーブルへの挿入 (オプション) */
    INSERT INTO ErrorLog (
       ErrorTime, 
       ErrorNumber, 
       ErrorMessage
    )
    VALUES (
       GETDATE(),
       ERROR_NUMBER(),
       ERROR_MESSAGE()
    );
 END CATCH;


また、上記で使用しているオプションのカスタムログテーブルを以下に示す。

 CREATE TABLE ErrorLog (
    ErrorTime DATETIME,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(4000)
    /* 必要に応じて他のカラムも追加 */
 );



「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_BULK_INSERT&oldid=12066」から取得