MochiuWiki : SUSE, EC, PCB

「SQL Server - BULK INSERT」の版間の差分

📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

← 古い編集
ビジュアルウィキテキスト
 
(同じ利用者による、間の7版が非表示)
51行目: 51行目:
<br><br>
<br><br>


== BULK INSERTの例 ==
== テーブルの作成 ==
下記は、CSVファイル(カンマ区切り)をインポートする際の例である。<br><br>
BULK INSERT文は、データの挿入のみを行うコマンドで、テーブルの作成機能は持っていない。<br>
* 文字コードがShift-JISの場合
データを挿入する場合は、列の定義や制約などのテーブル構造が必要となる。<br>
<br>
<syntaxhighlight lang="tsql">
CREATE TABLE customers (
    id    INT,
    name  VARCHAR(100),
    email VARCHAR(200)
);
</syntaxhighlight>
<br><br>
 
== 使用例 ==
====  カンマ区切りファイルのインポート ====
* FIRSTROWオプションでヘッダ行をスキップする。
* MAXERRORSオプションでエラー許容数を設定する。
* エラーログファイルを指定する。 (問題追跡が可能)
<br>
# CSVファイルの内容
John,Doe,30,New York
Jane,Smith,25,Los Angeles
<br>
<syntaxhighlight lang="tsql">
BULK INSERT Employees
FROM 'D:\Data\employees.csv'
WITH
(
    FIRSTROW = 2,                    /* ヘッダ行をスキップ */
    FIELDTERMINATOR = ',',            /* フィールド区切り文字 */
    ROWTERMINATOR = '\n',            /* 行区切り文字 */
    MAXERRORS = 2,                    /* 許容するエラーの最大数 */
    ERRORFILE = 'D:\Data\errors.txt'  /* エラーログファイル */
);
</syntaxhighlight>
<br>
==== タブ区切りファイルのインポート (日付フォーマット指定) ====
* DATE_FORMATで日付形式を明示的に指定する。
* FORMATオプションでCSV形式を指定する。
<br>
# TSVファイルの内容
OrderID    OrderDate    CustomerID    Amount
1001    2024-01-01    CUST001    1500.50
<br>
<syntaxhighlight lang="tsql">
BULK INSERT Orders
FROM 'D:\Data\orders.txt'
WITH
(
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '\t',    /* タブ区切り */
    ROWTERMINATOR = '\n',
    FORMAT = 'CSV',            /* CSVフォーマットを使用 */
    DATE_FORMAT = 'yyyy-MM-dd'  /* 日付フォーマットを指定 */
);
</syntaxhighlight>
<br>
==== 固定長フォーマットファイルのインポート ====
* フォーマットファイルを使用して列幅を定義する。
* KEEPNULLSオプションでNULL値の処理を制御する。
<br>
# データ例 (各フィールドが固定長)
ID  NAME      DEPT
001 JOHN      SALES
002 MARY      IT
<br>
<syntaxhighlight lang="tsql">
BULK INSERT Employees
FROM 'D:\Data\employees_fixed.txt'
WITH
(
    DATAFILETYPE = 'char',
    FORMATFILE = 'D:\Data\format.fmt',  /* フォーマットファイルを指定 */
    CODEPAGE = '65001',                /* UTF-8エンコーディング */
    KEEPNULLS                          /* NULL値を保持 */
);
</syntaxhighlight>
<br>
==== XMLフォーマットファイルを使用したインポート ====
* 詳細なデータマッピングが可能になる。
* CHECK_CONSTRAINTSオプションで制約チェックを有効化する。
* FIRE_TRIGGERSオプションでトリガー処理を制御する。
<br>
<syntaxhighlight lang="tsql">
/* より詳細なデータマッピングが必要な場合 */
BULK INSERT Products
FROM 'D:\Data\products.txt'
WITH
(
    FORMATFILE = 'D:\Data\products_format.xml',
    CHECK_CONSTRAINTS,                          /* 制約チェックを有効化 */
    FIRE_TRIGGERS,                              /* トリガーを発火 */
    KEEPIDENTITY                                /* ID列の値を保持 */
);
</syntaxhighlight>
<br>
==== バッチ処理によるBULK INSERT ====
* BATCHSIZEオプションとROWS_PER_BATCHオプションでパフォーマンス最適化する。
* TABLOCKオプションでロック戦略を制御する。
* ORDER句でデータの順序を指定する。
<br>
<syntaxhighlight lang="tsql">
/* 大量データを分割してインポートする場合 */
BULK INSERT LargeTable
FROM 'D:\Data\large_data.csv'
WITH
(
    BATCHSIZE = 1000,          /* バッチサイズを指定 */
    ROWS_PER_BATCH = 10000,    /* バッチあたりの行数 */
    TABLOCK,                  /* テーブルロックを使用 */
    ORDER (OrderColumn ASC)    /* データの順序を指定 */
);
</syntaxhighlight>
<br>
==== 文字コードに関する使用例 ====
以下の例では、CSVファイル (カンマ区切り) をインポートしている。<br>
<br>
  <syntaxhighlight lang="tsql">
  <syntaxhighlight lang="tsql">
/* 文字コードがShift-JISの場合 */
  BULK INSERT T_Table
  BULK INSERT T_Table
        FROM 'D:\UnicodeFile.txt'
FROM 'D:\UnicodeFile.txt'
        WITH (
WITH (
                DATAFILETYPE    = 'char',
    DATAFILETYPE    = 'char',
                FIELDTERMINATOR = ','
    FIELDTERMINATOR = ','
        );
);
  GO
  GO
  </syntaxhighlight>
  </syntaxhighlight>
<br>
<br>
* 文字コードがUNICODEの場合 (SQL Server 2014 R2以前)
*: ただし、SQL Server 2014 R2以前では、下記のクエリを実行しても UTF-8形式のファイルは取り込めない。
*: (UTF-8固有の文字が無いUTF-8形式のファイルはインポートできる)
  <syntaxhighlight lang="tsql">
  <syntaxhighlight lang="tsql">
/* 文字コードがUNICODEの場合 (SQL Server 2014 R2以前) */
/* ただし、SQL Server 2014 R2以前では、下記のクエリを実行しても UTF-8形式のファイルは取り込めない */
/* (UTF-8固有の文字が無いUTF-8形式のファイルはインポートできる) */
  BULK INSERT T_Table
  BULK INSERT T_Table
        FROM 'D:\UnicodeFile.txt'
FROM 'D:\UnicodeFile.txt'
        WITH (
WITH (
                DATAFILETYPE    = 'widechar',
    DATAFILETYPE    = 'widechar',
                FIELDTERMINATOR = ','
    FIELDTERMINATOR = ','
        );
);
  GO
  GO
  </syntaxhighlight>
  </syntaxhighlight>
<br>
<br>
* 文字コードがUTF-8の場合 (SQL Server 2014 R2以降)
*: SQL Server 2014 R2以降では、UTF-8形式のファイルをインポートすることが出来る。
  <syntaxhighlight lang="tsql">
  <syntaxhighlight lang="tsql">
/* 文字コードがUTF-8の場合 (SQL Server 2014 R2以降) */
/* SQL Server 2014 R2以降では、UTF-8形式のファイルをインポートすることが出来る */
  BULK INSERT T_Table
  BULK INSERT T_Table
        FROM 'D:\UnicodeFile.txt'
FROM 'D:\UnicodeFile.txt'
        WITH (
WITH (
                DATAFILETYPE    = 'char',
    DATAFILETYPE    = 'char',
                CODEPAGE        = '65001',
    CODEPAGE        = '65001',
                FIELDTERMINATOR = ','
    FIELDTERMINATOR = ','
        );
);
  GO
  GO
</syntaxhighlight>
<br><br>
== 動的にテーブルを作成 ==
BULK INSERT文は、データの挿入のみを行うコマンドで、テーブルの作成機能は持っていない。<br>
データを挿入する場合は、列の定義や制約などのテーブル構造が必要となる。<br>
<br>
既存のテーブル構造が不明な場合は、データファイルの内容を確認して、適切な列の型とサイズを決定してからテーブルを作成する。<br>
<br>
また、データファイルの内容を分析してテーブルを作成するプロセスをSQLで自動化することができる。<br>
<br>
以下の例では、まず、CSVファイルを一時テーブルに読み込み、ヘッダ行とサンプルデータを分析してデータ型を推測している。<br>
次に、各データ型である数値型、日付型、文字列型 (長さに応じて適切なサイズを設定) を推測して、CREATE TABLE文を使用して動的に生成して実行している。<br>
最後に、作成されたテーブル構造を確認している。<br>
<br>
※注意<br>
* データ型の推測は最初の行のみを基に行われるため、完全な精度は保証されない。
* 大規模なデータセットの場合、より多くのサンプルを分析することを推奨する。
* 特殊な要件 (一意制約、外部キー等) は手動で追加する必要がある。
<br>
このSQLを実行した後、必要に応じてALTER TABLE文を使用して列定義を調整できる。<br>
<br>
<syntaxhighlight lang="tsql">
/* 一時テーブルを作成して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');
</syntaxhighlight>
<br><br>
== エラーハンドリング ==
<syntaxhighlight lang="tsql">
/* 基本的なエラーハンドリングの設定 */
BULK INSERT TargetTable
FROM 'C:\Data\source.txt'
WITH
(
    ERRORFILE = 'C:\Errors\error.txt',
    MAXERRORS = 10,
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
</syntaxhighlight>
<br>
* ERRORFILEオプション
*: エラーが発生した行の詳細情報を指定されたファイルに出力する。
*: このファイルには、エラーの発生した行番号、エラーの種類、具体的なエラーメッセージ等が記録される。
*: <br>
* MAXERRORSオプション (重要)
*: 許容するエラーの最大数を指定する。
*: この数を超えるとBULK INSERT処理全体が中止される。
*: 例えば、MAXERRORSオプションを10に設定した場合、11個目のエラーが発生した時点で処理が停止する。
*: <br>
* CHECK_CONSTRAINTSオプション
*: データの検証に関するエラーハンドリングの設定である。
*: このオプションをONに設定する場合、制約違反のチェックが行われる。
*: ただし、パフォーマンスへの影響があるため、データの信頼性が確保されている場合はOFFにする。
*: <br>
* KEEPNULLSオプション
*: 空の文字列をNULLとして扱うかどうかを制御する。
*: これにより、NULL制約に関連するエラーを適切に処理することができる。
<br>
また、詳細なエラー情報を取得する場合は、TRY-CATCHブロックを使用する。<br>
<syntaxhighlight lang="tsql">
/* 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
</syntaxhighlight>
<br>
実務では、エラーファイルの内容を定期的に監視して、必要に応じて修正アクションを取ることが推奨される。<br>
エラーファイルには、以下に示すような情報が含まれる。<br>
* エラーが発生した行番号
* エラーの発生した列
* エラーの種類 (データ型の不一致、制約違反等)
* 具体的なエラーメッセージ
<br>
エラー発生時のロールバック動作についても理解が重要である。<br>
<code>BATCHSIZE</code>オプションを使用している場合、エラーが発生したバッチのみがロールバックされて、それ以前に正常に取り込まれたデータは保持される。<br>
<br>
<syntaxhighlight lang="tsql">
/* ターゲットテーブルに対する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;
</syntaxhighlight>
<br>
ただし、実務では、以下に示すように動的なパラメータを使用することが一般的である。<br>
変数を使用することにより柔軟な運用が可能になり、また、パラメータ化することで再利用性も高まる。<br>
<syntaxhighlight lang="tsql">
/* 動的パラメータの使用 */
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;
</syntaxhighlight>
<br>
また、上記で使用しているオプションのカスタムログテーブルを以下に示す。<br>
<syntaxhighlight lang="tsql">
CREATE TABLE ErrorLog (
    ErrorTime DATETIME,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(4000)
    /* 必要に応じて他のカラムも追加 */
);
  </syntaxhighlight>
  </syntaxhighlight>
<br><br>
<br><br>
「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_BULK_INSERT」から取得