MochiuWiki : SUSE, EC, PCB

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

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

← 古い編集
ビジュアルウィキテキスト
 
(同じ利用者による、間の3版が非表示)
49行目: 49行目:
<br>
<br>
BULK INSERT文は、大量データの取り込みに非常に効果的なツールであるが、適切な設定と事前準備が重要となる。<br>
BULK INSERT文は、大量データの取り込みに非常に効果的なツールであるが、適切な設定と事前準備が重要となる。<br>
<br><br>
== テーブルの作成 ==
BULK INSERT文は、データの挿入のみを行うコマンドで、テーブルの作成機能は持っていない。<br>
データを挿入する場合は、列の定義や制約などのテーブル構造が必要となる。<br>
<br>
<syntaxhighlight lang="tsql">
CREATE TABLE customers (
    id    INT,
    name  VARCHAR(100),
    email VARCHAR(200)
);
</syntaxhighlight>
<br><br>
<br><br>


== 使用例 ==
== 使用例 ==
====  カンマ区切りファイルのインポート ====
====  カンマ区切りファイルのインポート ====
* FIRSTROWオプションでヘッダ行をスキップする。
* MAXERRORSオプションでエラー許容数を設定する。
* エラーログファイルを指定する。 (問題追跡が可能)
<br>
  # CSVファイルの内容
  # CSVファイルの内容
  John,Doe,30,New York
  John,Doe,30,New York
62行目: 79行目:
  WITH
  WITH
  (
  (
     FIRSTROW = 2,                    /* ヘッダー行をスキップ */
     FIRSTROW = 2,                    /* ヘッダ行をスキップ */
     FIELDTERMINATOR = ',',            /* フィールド区切り文字 */
     FIELDTERMINATOR = ',',            /* フィールド区切り文字 */
     ROWTERMINATOR = '\n',            /* 行区切り文字 */
     ROWTERMINATOR = '\n',            /* 行区切り文字 */
71行目: 88行目:
<br>
<br>
==== タブ区切りファイルのインポート (日付フォーマット指定) ====
==== タブ区切りファイルのインポート (日付フォーマット指定) ====
* DATE_FORMATで日付形式を明示的に指定する。
* FORMATオプションでCSV形式を指定する。
<br>
  # TSVファイルの内容
  # TSVファイルの内容
  OrderID    OrderDate    CustomerID    Amount
  OrderID    OrderDate    CustomerID    Amount
89行目: 109行目:
<br>
<br>
==== 固定長フォーマットファイルのインポート ====
==== 固定長フォーマットファイルのインポート ====
* フォーマットファイルを使用して列幅を定義する。
* KEEPNULLSオプションでNULL値の処理を制御する。
<br>
  # データ例 (各フィールドが固定長)
  # データ例 (各フィールドが固定長)
  ID  NAME      DEPT
  ID  NAME      DEPT
107行目: 130行目:
<br>
<br>
==== XMLフォーマットファイルを使用したインポート ====
==== XMLフォーマットファイルを使用したインポート ====
* 詳細なデータマッピングが可能になる。
* CHECK_CONSTRAINTSオプションで制約チェックを有効化する。
* FIRE_TRIGGERSオプションでトリガー処理を制御する。
<br>
  <syntaxhighlight lang="tsql">
  <syntaxhighlight lang="tsql">
  /* より詳細なデータマッピングが必要な場合 */
  /* より詳細なデータマッピングが必要な場合 */
122行目: 149行目:
<br>
<br>
==== バッチ処理によるBULK INSERT ====
==== バッチ処理によるBULK INSERT ====
* BATCHSIZEオプションとROWS_PER_BATCHオプションでパフォーマンス最適化する。
* TABLOCKオプションでロック戦略を制御する。
* ORDER句でデータの順序を指定する。
<br>
  <syntaxhighlight lang="tsql">
  <syntaxhighlight lang="tsql">
  /* 大量データを分割してインポートする場合 */
  /* 大量データを分割してインポートする場合 */
143行目: 174行目:
   
   
  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>
156行目: 187行目:
  /* (UTF-8固有の文字が無い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>
168行目: 199行目:
  /* SQL Server 2014 R2以降では、UTF-8形式のファイルをインポートすることが出来る */
  /* 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」から取得