MochiuWiki : SUSE, EC, PCB

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

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

← 古い編集
ビジュアルウィキテキスト
 
(同じ利用者による、間の4版が非表示)
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">
  <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">
/* 文字コードが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>
  </syntaxhighlight>
<br><br>
<br><br>
「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_BULK_INSERT」から取得