次の方法で共有

  • [アーティクル]

BULK INSERT (Transact-SQL)

ユーザーが指定した形式で、データベース テーブルまたはビュー内にデータ ファイルをインポートします。

トピック リンク アイコンTransact-SQL 構文表記規則

構文

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
        ( 
   [ [ , ] BATCHSIZE = batch_size ] 
      [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
      [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
        )]

引数

  • database_name
    指定のテーブルまたはビューが含まれているデータベース名を指定します。指定しない場合、現在のデータベースが使用されます。

  • schema_name
    テーブル スキーマまたはビュー スキーマの名前を指定します。一括インポート操作を実行するユーザーの既定のスキーマが、指定したテーブルまたはビューのスキーマと同じ場合、schema_name は省略可能です。schema を指定せず、さらに一括インポート操作を実行するユーザーの既定のスキーマが、指定したテーブルまたはビューのスキーマと異なる場合、SQL Server ではエラー メッセージが返され、一括インポート操作はキャンセルされます。

  • table_name
    データの一括インポート先のテーブル名またはビュー名を指定します。指定できるビューは、すべての列が同じベース テーブルを参照するビューだけです。データをビューに読み込むときの制限の詳細については、「INSERT (Transact-SQL)」を参照してください。

  • 'data_file'
    指定のテーブルまたはビューにインポートするデータが含まれているデータ ファイルのフル パスを指定します。BULK INSERT を使用して、ディスク (ネットワーク、フロッピー ディスク、ハード ディスクなど) からデータをインポートすることができます。

    data_file には、SQL Server が実行されているサーバーからの有効なパスを指定する必要があります。data_file がリモート ファイルの場合は、UNC (汎用名前付け規則) 名を指定します。UNC 名の形式は、\\Systemname\ShareName\Path\FileName です。たとえば、\\SystemX\DiskZ\Sales\update.txt のように指定します。

  • BATCHSIZE **=**batch_size
    1 つのバッチに含まれている行の数を指定します。それぞれのバッチは、1 回のトランザクションでサーバーにコピーされます。コピーに失敗した場合、SQL Server では各バッチのトランザクションがコミットまたはロールバックされます。既定では、指定のデータ ファイル内にあるすべてのデータが 1 つのバッチになります。パフォーマンスに関する考慮事項については、後の「解説」を参照してください。

    詳細については、「一括インポートのバッチの管理」を参照してください。

  • CHECK_CONSTRAINTS
    一括インポート操作中、対象テーブルまたはビューに対するすべての制約を検証します。CHECK_CONSTRAINTS オプションを指定しない場合、CHECK 制約および FOREIGN KEY 制約は無視され、操作の後でテーブルの制約は信頼されていないものとしてマークされます。

    注意注意

    UNIQUE、PRIMARY KEY、および NOT NULL 制約は常に適用されます。

    テーブル全体の制約は、任意の時点で必ず検証してください。一括インポート操作の実行時にテーブルが空でなかった場合は、制約の再検証を行うと、追加データに CHECK 制約を適用するよりもコストがかかる可能性があります。

    入力データに制約違反の行が含まれている場合などは、制約を無効 (既定の動作) にできます。制約の CHECK を無効にした場合は、データをインポートした後 Transact-SQL ステートメントを使用して無効なデータを削除できます。

    注意注意

    MAXERRORS オプションは制約チェックには適用されません。
    注意注意

    SQL Server 2005 以降のバージョンでは、BULK INSERT によって実行される新しいデータ検証とデータ チェックが原因で、データ ファイルのデータが無効な場合にそのデータを使用して既存のスクリプトを実行すると、スクリプトが失敗する可能性があります。

    詳細については、「一括インポート操作による制約チェックの制御」を参照してください。

  • CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
    データ ファイル内のデータのコード ページを指定します。CODEPAGE は、127 より大きいか、32 より小さい文字値の char、varchar、または text 型の列がデータに含まれる場合にのみ適用されます。

    注意注意

    フォーマット ファイルの各列に対して照合順序名を指定することをお勧めします。

    CODEPAGE の値

    説明

    ACP

    char、varchar、または text データ型の列は、ANSI/Microsoft Windows コード ページ (ISO 1252) から SQL Server コード ページに変換されます。

    OEM (既定値)

    char、varchar、または text のデータ型の列は、システムの OEM コード ページから SQL Server コード ページに変換されます。

    RAW

    1 つのコード ページから別のコード ページへの変換は行われません。このオプションを使用すると、最も高速に操作を完了できます。

    code_page

    850 など、特定のコード ページ番号を指定します。

    重要な注意事項重要
    SQL Server は、コード ページ 65001 (UTF-8 エンコード) をサポートしません。

    詳細については、「異なる照合順序間でのデータのコピー」を参照してください。

  • DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
    BULK INSERT で、指定したデータ ファイルの型の値に基づいてインポート操作を実行します。

    DATAFILETYPE の値

    すべてのデータが示す形式

    char (既定値)

    文字形式。

    詳細については、「文字形式を使用したデータのインポートまたはエクスポート」を参照してください。

    native

    ネイティブ (データベース) データ型。bcp ユーティリティを使用して SQL Server からデータを一括インポートし、ネイティブ データ ファイルを作成します。

    ネイティブ値を使用すると、char 型の値を使用するよりもパフォーマンスが向上します。

    詳細については、「ネイティブ形式を使用したデータのインポートまたはエクスポート」を参照してください。

    widechar

    Unicode 文字。

    詳細については、「Unicode 文字形式を使用したデータのインポートまたはエクスポート」を参照してください。

    widenative

    ネイティブ (データベース) データ型。ただし、データが Unicode として格納される、char、varchar、text 列は除きます。bcp ユーティリティを使用して SQL Server からデータを一括インポートし、widenative データ ファイルを作成します。

    widenative 値を使用すると、widechar 値を使用するよりもパフォーマンスが向上します。データ ファイルに ANSI 拡張文字が含まれている場合は、widenative を指定します。

    詳細については、「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート」を参照してください。

  • FIELDTERMINATOR ='field_terminator'
    char および widechar 型のデータ ファイルに使用するフィールド ターミネータを指定します。既定のフィールド ターミネータは \t (タブ文字) です。詳細については、「フィールド ターミネータと行ターミネータの指定」を参照してください。

  • FIRSTROW **=**first_row
    読み込み開始行の行番号を指定します。既定値は、指定されたデータ ファイルの先頭行です。FIRSTROW は 1 から始まります。

    注意注意

    FIRSTROW 属性は、列ヘッダーのスキップを目的としたものではありません。ヘッダーのスキップは、BULK INSERT ステートメントではサポートされません。行をスキップした場合、SQL Server データベース エンジンではフィールド ターミネータのみが調べられます。スキップした行のフィールドに含まれているデータの有効性は確認されません。

  • FIRE_TRIGGERS
    一括読み込みの操作中に、インポート先のテーブルで定義されている挿入トリガを実行します。対象テーブルで INSERT 操作にトリガが定義されている場合、そのトリガは完了した各バッチに対して実行されます。

    FIRE_TRIGGERS が指定されていない場合、挿入トリガは実行されません。

    詳細については、「データの一括インポート時のトリガ実行の制御」を参照してください。

  • FORMATFILE ='format_file_path'
    フォーマット ファイルの完全なパスを指定します。フォーマット ファイルには、格納済みの応答を含むデータ ファイルの内容が記述されています。これらの応答は同じテーブルまたはビューに対し bcp ユーティリティを実行して作成されたものです。フォーマット ファイルは次の場合に使用します。

    • データ ファイルに含まれる列の数が、テーブルまたはビューより多い、または少ない。

    • 列の順序が異なる。

    • 列の区切り記号が異なる。

    • データ形式に他に異なる点がある。フォーマット ファイルは通常、bcp ユーティリティを使用して作成し、必要に応じてテキスト エディタで修正します。詳細については、「bcp ユーティリティ」を参照してください。

  • KEEPIDENTITY
    インポートしたデータ ファイルの ID 値が、ID 列で使用されます。KEEPIDENTITY を指定しない場合、この列の ID 値は検証のみが行われ、インポートされません。この場合 SQL Server では、テーブルの作成時に指定された seed と増分値に基づいて、一意な値が自動的に割り当てられます。データ ファイルにテーブルまたはビュー内の ID 列の値が含まれない場合は、フォーマット ファイルを使用して、データのインポート時にテーブルまたはビュー内の ID 列をスキップするよう指定します。SQL Server ではこの列に一意な値が自動的に割り当てられます。詳細については、「DBCC CHECKIDENT (Transact-SQL)」を参照してください。

    ID 値の保持の詳細については、「データの一括インポート時の ID 値の保持」を参照してください。

  • KEEPNULLS
    一括インポート操作時、空の列が挿入される場合は NULL 値が保持されます。その列の既定値は格納されません。詳細については、「一括インポート中の NULL の保持または既定値の使用」を参照してください。

  • KILOBYTES_PER_BATCH = kilobytes_per_batch
    バッチあたりのデータの概算キロバイト数 (KB) を kilobytes_per_batch として指定します。KILOBYTES_PER_BATCH の既定値はありません。パフォーマンスに関する考慮事項については、後の「解説」を参照してください。

    詳細については、「一括インポートのバッチの管理」を参照してください。

  • LASTROW**=**last_row
    読み込み終了行の行番号を指定します。既定値は 0 で、これは指定のデータ ファイルの最終行を表します。

  • MAXERRORS = max_errors
    一括インポート操作時に許容されるデータの構文エラーの最大数を指定します。この最大数に達すると、操作はキャンセルされます。一括インポート操作でインポートできない行は無視され、それぞれ 1 つのエラーとしてカウントされます。max_errors を指定しない場合の既定値は 10 です。

    注意注意

    MAX_ERRORS オプションは、制約チェックや money および bigint のデータ型の変換には適用されません。

  • ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
    データ ファイル内のデータの並べ替え方法を指定します。インポートするデータをテーブル上のクラスタ化インデックスに従って並べ替えると、一括インポートのパフォーマンスが向上します。データ ファイルが異なる順序で並んでいる場合、つまりクラスタ化インデックス キーの順序以外の順で並んでいるか、テーブルにクラスタ化インデックスが存在しない場合、ORDER 句は無視されます。指定する列の名前は、インポート先のテーブル内で有効な列の名前であることが必要です。既定では、一括挿入操作はデータ ファイルが並べ替えられていないことを前提に実行されます。最適な一括インポートのため、SQL Server では、インポートするデータが並べ替えられているかどうかも確認されます。

    詳細については、「データの一括インポート時の並べ替え順の制御」を参照してください。

  • n
    複数の列を指定できることを示すプレースホルダです。

  • ROWS_PER_BATCH **=**rows_per_batch
    データ ファイル内にあるデータ行の概算数を指定します。

    既定では、データ ファイル内のすべてのデータは単一のトランザクションとしてサーバーに送られ、バッチ内の行数はクエリ オプティマイザには通知されません。ROWS_PER_BATCH を値 > 0 で指定した場合、サーバーでは一括インポート操作の最適化にこの値が使用されます。ROWS_PER_BATCH に指定する値は、実際の行数とほぼ同じにする必要があります。パフォーマンスに関する考慮事項については、後の「解説」を参照してください。

    詳細については、「一括インポートのバッチの管理」を参照してください。

  • ROWTERMINATOR ='row_terminator'
    char および widechar 型のデータ ファイルに使用する行ターミネータを指定します。既定の行ターミネータは \r\n (改行文字) です。詳細については、「フィールド ターミネータと行ターミネータの指定」を参照してください。

  • TABLOCK
    一括インポート操作中にテーブル レベルのロックを取得します。テーブルにインデックスがなく、TABLOCK を指定した場合は、複数のクライアントで同時に 1 つのテーブルを読み込むことができます。既定では、ロック動作はテーブル オプション table lock on bulk load によって決定されます。一括インポート操作中にロックを維持すると、テーブル ロックの競合が少なくなるので、場合によってはパフォーマンスが大幅に向上します。パフォーマンスに関する考慮事項については、後の「解説」を参照してください。

    詳細については、「一括インポートのロック動作の制御」を参照してください。

  • ERRORFILE ='file_name'
    形式エラーがあり、OLE DB 行セットに変換できない行を収集するときに使用するファイルを指定します。該当する行は、データ ファイルからこのエラー ファイルに "そのまま" コピーされます。

    このエラー ファイルは、コマンドが実行されたときに作成されます。ファイルが既に存在する場合はエラーが発生し、拡張子 .ERROR.txt を持つ制御ファイルが作成されます。このファイルにはエラー ファイルの各行の参照と、エラーの診断が含まれています。エラーが修正されるとすぐ、データは読み込み可能になります。

説明

BULK INSERT ステートメント、INSERT ... SELECT * FROM OPENROWSET(BULK...) ステートメント、および bcp コマンドの比較については、「一括インポート操作と一括エクスポート操作について」を参照してください。

CSV データ ファイルからデータをインポートするための要件など、一括インポートのデータの準備については、「一括エクスポートまたは一括インポートのデータの準備」を参照してください。

BULK INSERT ステートメントは、ユーザー定義のトランザクション内で実行できます。ユーザー定義のトランザクションで、複数のバッチによってテーブルまたはビューにデータをインポートするために BULK INSERT ステートメントと BATCHSIZE 句を使用した場合、このトランザクションをロールバックすると、SQL Server に送信されたバッチがすべてロールバックされます。

一括インポート中に実行される行挿入操作がトランザクション ログに記録されるタイミングの詳細については、「一括インポートで最小ログ記録を行うための前提条件」を参照してください。

SQL Server 2005 以降のバージョンでは、BULK INSERT によって、ファイルから読み込んだデータに対して新しいデータ検証とデータ チェックが実行されます。これらは以前よりも厳密なため、無効なデータを使用して既存のスクリプトを実行すると、スクリプトは失敗する可能性があります。たとえば、BULK INSERT では新たに次の検証が行われます。

  • float 型と real 型のネイティブ表記が有効かどうか。

  • Unicode データが偶数バイト長かどうか。

無効なデータ形式は、以前のバージョンの SQL Server で一括インポート可能でも、現在では読み込めない場合があります。以前のバージョンの SQL Server では、クライアントが無効なデータにアクセスを試行するまで、エラーは発生しません。現在は検証機能が強化されているため、一括インポート後のデータのクエリで発生する問題を最小限に抑えられます。

パフォーマンスに関する考慮事項

1 つのバッチでフラッシュされるページの数が内部しきい値を超えると、バッチのコミット時にフラッシュするページを特定するためにバッファ プールのフル スキャンが行われる可能性があります。フル スキャンが行われると、一括インポートのパフォーマンスが低下します。この内部しきい値の問題は、大きなバッファ プールと遅い I/O サブシステムの組み合わせでも発生します。大規模なコンピュータでバッファ オーバーフローを防ぐには、TABLOCK ヒントを使用しないようにするか (一括インポートの最適化は行われなくなります)、バッチ サイズを小さくします (一括インポートの最適化は引き続き行われます)。

コンピュータはそれぞれ異なるため、実際のデータでさまざまなバッチ サイズを試して最適な値を見つけるようにすることをお勧めします。

SQLXML ドキュメントの一括エクスポートまたは一括インポート

SQLXML データを一括エクスポートまたは一括インポートするには、フォーマット ファイルで次のいずれかのデータ型を使用します。

データ型

結果

SQLCHAR または SQLVARYCHAR

データは、クライアント コード ページまたは照合順序で暗黙的に指定されるコード ページで送られます。フォーマット ファイルを指定せずに DATAFILETYPE ='char' を指定した場合と同じ結果が得られます。

SQLNCHAR または SQLNVARCHAR

データは Unicode として送られます。フォーマット ファイルを指定せずに DATAFILETYPE = 'widechar' を指定した場合と同じ結果が得られます。

SQLBINARY または SQLVARYBIN

データは変換なしで送られます。

文字列から 10 進数への型変換

SQL Server 2005 以降のバージョンでは、BULK INSERT で実行される文字列から 10 進数への型変換には、Transact-SQL の CONVERT 関数と同じ規則が適用されるので、科学的表記法を使用した数値を表す文字列は拒否されます。したがって、BULK INSERT を実行するときに、そのような文字列が無効な値として評価され、変換エラーが報告されます。

注意注意

SQL Server Version 7.0 および SQL Server 2000 の BULK INSERT では、科学的表記法を使用する数値を表す文字列に対して、文字列から 10 進数への型変換がサポートされます。

この問題を回避するには、科学的表記法の float 型のデータを 10 進数の列に一括インポートするフォーマット ファイルを使用します。フォーマット ファイルには、列のデータを明示的に real または float 型として記述します。これらのデータ型の詳細については、「float 型と real 型 (Transact-SQL)」を参照してください。

注意注意

フォーマット ファイルでは、real 型のデータが SQLFLT4 データ型として、float 型のデータが SQLFLT8 データ型として表現されます。XML フォーマット ファイルの詳細については、「XML フォーマット ファイルのスキーマ構文」を参照してください。XML 以外のフォーマット ファイルの詳細については、「bcp を使用したファイル ストレージ型の指定」を参照してください。

科学的表記法を使用した数値をインポートする例

この例では、次のテーブルを使用します。

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

ここでの目的は、t_float テーブルにデータを一括インポートすることです。データ ファイル C:\t_float-c.dat には、次のような科学的表記法の float 型のデータが含まれています。

8.0000000000000002E-28.0000000000000002E-2

しかし、テーブルの 2 番目の列 c2 で decimal データ型を使用しているので、このデータを BULK INSERT によって t_float に直接インポートすることはできません。そのため、フォーマット ファイルが必要です。フォーマット ファイルでは、科学的表記法の float 型のデータを列 c2 の 10 進形式にマップする必要があります。

次のフォーマット ファイルでは、SQLFLT8 データ型を使用して、2 番目のデータ フィールドを 2 番目の列にマップしています。

<?xml version="1.0"?>

<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

このフォーマット ファイル (ファイル名 C:\t_floatformat-c-xml.xml) を使用してテスト テーブルにテスト データをインポートするには、次の Transact-SQL ステートメントを実行します。

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

権限

INSERT および ADMINISTER BULK OPERATIONS 権限が必要です。ただし次の操作を 1 つ以上行う場合は、さらに ALTER TABLE 権限が必要になります。

  • 制約が存在する場合に、CHECK_CONSTRAINTS オプションを指定しない。

    注意注意

    制約の無効化は既定の動作です。制約を明示的に検証するには、CHECK_CONSTRAINTS オプションを使用します。

  • トリガが存在する場合に、FIRE_TRIGGER オプションを指定しない。

    注意注意

    既定では、トリガは起動しません。トリガを明示的に起動するには、FIRE_TRIGGERS オプションを使用します。

  • KEEPIDENTITY オプションを使用して、データ ファイルから ID 値をインポートする。

セキュリティ アカウントの委任 (権限借用)

SQL Server ユーザーが Windows 認証を使用してログインした場合、そのユーザーは SQL Server プロセスのセキュリティ プロファイルに関係なく、そのユーザー アカウントでアクセス可能なファイルだけを読み取ることができます。

あるコンピュータで sqlcmd または osql を使用して BULK INSERT ステートメントを実行し、2 台目のコンピュータの SQL Server にデータを挿入し、UNC パスを使用して 3 台目のコンピュータの data_file を指定した場合、エラー 4861 が返されることがあります。

この問題を解決するには、SQL Server 認証を使用し、SQL Server プロセス アカウントのセキュリティ プロファイルを使用する SQL Server ログインを指定します。または、Windows の構成でセキュリティ アカウントの委任を有効にします。ユーザー アカウントの信頼性を委任の対象として有効にする方法の詳細については、Windows ヘルプを参照してください。

このエラーの詳細について、および BULK INSERT を使用する場合のセキュリティに関するその他の考慮事項については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」を参照してください。

例

A. ファイルからのデータのインポートにパイプを使用する

次の例では、パイプ (|) をフィールド ターミネータ、|\n を行ターミネータとして使用し、指定のデータ ファイルから AdventureWorks.Sales.SalesOrderDetail テーブルに、注文の詳細情報をインポートします。

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      )

B. FIRE_TRIGGERS 引数を使用する

次の例では、FIRE_TRIGGERS 引数を指定します。

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )

C. 行ターミネータとしてライン フィードを使用する

次の例では、UNIX 出力などのように、ライン フィードを行ターミネータとして使用するファイルをインポートします。

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

その他の例

BULK INSERT のその他の例については、次のトピックを参照してください。

変更履歴

変更内容

「解説」に「パフォーマンスに関する考慮事項」を追加しました。

FIRSTROW オプションの説明を更新しました。

code_page の説明を更新しました。