BULK INSERT (Transact-SQL)

[アーティクル]
03/24/2023

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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

構文

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] DATA_SOURCE = 'data_source_name' ]

   -- text formatting options
   [ [ , ] CODEPAGE = { 'RAW' | 'code_page' | 'ACP' | 'OEM' } ]
   [ [ , ] DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' } ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']

   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] LASTROW = last_row ]

   -- input file format options
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   -- error handling options
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]

   -- database options
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] TABLOCK ]

   -- source options
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch 
   [ [ , ] BATCHSIZE = batch_size ]

    )]

引数

database_name

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

schema_name

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

table_name

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

FROM 'data_file'

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

data_file には、SQL Server が実行されているサーバーからの有効なパスを指定する必要があります。 data_file がリモートファイルの場合は、UNC (汎用名前付け規則) 名を指定します。 UNC 名の形式は、\\SystemName\ShareName\Path\FileName です。例:

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.dat';

SQL Server 2017 (14.x) 以降では、data_file は Azure Blob Storage に格納することができます。その場合は、data_source_name オプションを指定する必要があります。例については、「Azure Blob Storage 内のファイルからデータをインポートする」を参照してください。

Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

BATCHSIZE = batch_size

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

CHECK_CONSTRAINTS

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

UNIQUE および PRIMARY KEY 制約は常に適用されます。 NOT NULL 制約で定義された文字型列にインポートする場合、テキストファイルに値がなければ BULK INSERT は空白文字列を挿入します。

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

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

Note

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

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

データファイル内のデータのコードページを指定します。 CODEPAGE は、データに char、varchar、text 列 (文字値が 127 より大きいか、32 未満) が含まれている場合にのみ当てはまります。例については、「コードページを指定する」を参照してください。

SQL Server 2017 (14.x) の場合、CODEPAGE オプションは Linux 上ではサポートされていません。 SQL Server 2019 (15.x) の場合、CODEPAGE に対して使えるのは 'RAW' オプションのみです。

フォーマットファイルの各列に対して照合順序名を指定する必要があります。

CODEPAGE の値	説明
ACP	char、varchar、または text データ型の列は、ANSI/Microsoft Windows コードページ (ISO 1252) から SQL Server コードページに変換されます。
OEM (既定値)	char、varchar、または text のデータ型の列は、システムの OEM コードページから SQL Server コードページに変換されます。
RAW	コードページの変換は行われません。 RAW は最も高速なオプションです。
code_page	850 など、特定のコードページ番号を指定します。 SQL Server 2016 (13.x) より前のバージョンでは、コードページ 65001 (UTF-8 エンコード) がサポートされません。

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

BULK INSERT で、指定したデータファイルの型の値に基づいてインポート操作を実行します。

DATAFILETYPE の値	すべてのデータが示す形式
char (既定値)	文字形式。詳細については、「文字形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。
native	ネイティブ (データベース) データ型。 bcp ユーティリティを使用して SQL Server からデータを一括インポートし、ネイティブデータファイルを作成します。ネイティブ値を使用すると、char 型の値を使用するよりもパフォーマンスが向上します。ネイティブ形式は、拡張文字や 2 バイト文字セット (DBCS) の文字を含まないデータファイルを使用して、SQL Server の複数のインスタンス間でデータを一括転送する場合に推奨します。詳細については、「ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。
widechar	Unicode 文字。詳細については、「Unicode 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。
widenative	ネイティブ (データベース) データ型。ただし、データが Unicode として格納される char、varchar、text 列は除きます。 bcp ユーティリティを使用して SQL Server からデータを一括インポートし、widenative データファイルを作成します。 widenative 値を使用すると、widechar 値を使用するよりもパフォーマンスが向上します。データファイルに ANSI 拡張文字が含まれている場合は、widenative を指定します。詳細については、「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート (SQL Server)」を参照してください。

DATA_SOURCE = 'data_source_name'

適用対象: SQL Server 2017 (14.x) および Azure SQL データベース。

インポートされるファイルの Azure Blob Storage の場所を指している名前付きの外部データソースを指定します。外部データソースは、SQL Server 2017 (14.x) で追加された TYPE = BLOB_STORAGE オプションを使用して作成する必要があります。詳しくは、「CREATE EXTERNAL DATA SOURCE」をご覧ください。例については、「Azure Blob Storage 内のファイルからデータをインポートする」を参照してください。

ERRORFILE = 'error_file_path'

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

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

SQL Server 2017 (14.x) 以降では、error_file_path は Azure Blob Storage に格納することができます。

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

適用対象: SQL Server 2017 (14.x)。

インポート中に見つかったエラーを格納するエラーファイルの Azure Blob Storage の場所を指している名前付きの外部データソースを指定します。外部データソースは、SQL Server 2017 (14.x) で追加された TYPE = BLOB_STORAGE オプションを使用して作成する必要があります。詳しくは、「CREATE EXTERNAL DATA SOURCE」をご覧ください。

FIRSTROW = first_row

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

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

FIRE_TRIGGERS

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

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

FORMATFILE_DATA_SOURCE = 'data_source_name'

適用対象: SQL Server 2017 (14.x)。

インポートされるデータのスキーマを定義するフォーマットファイルの Azure Blob Storage の場所を指している名前付きの外部データソースを指定します。外部データソースは、SQL Server 2017 (14.x) で追加された TYPE = BLOB_STORAGE オプションを使用して作成する必要があります。詳しくは、「CREATE EXTERNAL DATA SOURCE」をご覧ください。

KEEPIDENTITY

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

ID 値の保持について詳しくは、「データの一括インポート時の ID 値の保持 (SQL Server)」をご覧ください。

KEEPNULLS

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

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 に指定する値は、実際の行数とほぼ同じにする必要があります。パフォーマンスに関する考慮事項については、この記事で後述する「パフォーマンスに関する考慮事項」を参照してください。

TABLOCK

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

列ストアインデックスの場合、複数の行セットに内部的に分かれているため、ロックの動作が異なります。各スレッドは、行セットに対して X ロックを取得して同時実行データ読み込みセッションによる並列データ読み込みを許可することで、それぞれの行セットにデータを排他的に読み込みます。 TABLOCK オプションを使用すると、スレッドは (従来の行セットの BU ロックとは異なり) テーブルに対して X ロックを取得し、他の同時実行スレッドが同時にデータを読み込むのを防ぎます。

入力ファイルフォーマットのオプション

FORMAT = 'CSV'

適用対象: SQL Server 2017 (14.x)。

RFC 4180 標準に準拠しているコンマ区切り値ファイルを指定します。

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

FIELDQUOTE = 'field_quote'

適用対象: SQL Server 2017 (14.x)。

CSV ファイルで引用符文字として使用される文字を指定します。指定されていない場合は、RFC 4180 標準の定義に従って引用符文字 (") が引用符文字として使用されます。

FORMATFILE = 'format_file_path'

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

データファイルに含まれる列の数が、テーブルまたはビューより多い、または少ない。
列の順序が異なる。
列の区切り記号が異なる。
データ形式に他に変更点がある。フォーマットファイルは通常、bcp ユーティリティを使用して作成し、必要に応じてテキストエディターで修正します。詳細については、「bcp ユーティリティ」と「フォーマットファイルの作成」を参照してください。

SQL Server 2017 (14.x) 以降、Azure SQL Database では、format_file_path は Azure Blob Storage に格納することができます。

FIELDTERMINATOR = 'field_terminator'

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

ROWTERMINATOR = 'row_terminator'

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

互換性

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

float データ型または real データ型のネイティブ表記が有効かどうか。
Unicode データが偶数バイト長かどうか。

データ型

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

BULK INSERT で使用される文字列から 10 進数へのデータ型変換には、Transact-SQL の CONVERT 関数と同じ規則が適用されるので、科学的表記法を使用した数値を表す文字列は拒否されます。したがって、BULK INSERT では、そのような文字列は無効な値として処理され、変換エラーが報告されます。

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

フォーマットファイル real データとして、 SQLFLT4 データ型と float データとして、 SQLFLT8 データ型。 XML 以外のフォーマットファイルについては、「bcp を使用したファイルストレージ型の指定 (SQL Server)」をご覧ください。

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

この例では、bulktest データベースの次のテーブルを使用します。

CREATE TABLE dbo.t_float(c1 FLOAT, c2 DECIMAL (5,4));

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

8.0000000000000002E-2 8.0000000000000002E-2

このサンプルをコピーするときは、タブ文字 (\t) をスペースとして保存するさまざまなテキストエディターとエンコードに注意してください。このサンプルでは、後でタブ文字を指定する必要があります。

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

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

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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.dbo.t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE = 'C:\t_floatformat-c-xml.xml');

重要

Azure SQL データベースでサポートされるのは、Azure Blob Storage からの読み取りのみです。

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

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

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

解説

BULK INSERT ステートメント、INSERT ... SELECT * FROM OPENROWSET(BULK...) ステートメントおよび bcp コマンドについては、「データの一括インポートと一括エクスポート (SQL Server)」をご覧ください。

データを一括インポート用に準備する方法については、「一括エクスポートまたは一括インポートのデータの準備 (SQL Server)」をご覧ください。

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

相互運用性

CSV ファイルからデータをインポートします

SQL Server 2017 (14.x) 以降では、Azure SQL Database と同じように、BULK INSERT で CSV 形式がサポートされます。

SQL Server 2017 (14.x) より前は、SQL Server の一括インポート操作ではコンマ区切り値 (CSV) ファイルがサポートされていません。ただし、場合によっては、 SQL Serverに対してデータを一括インポートする際、CSV ファイルをデータファイルとして使用できます。 CSV データファイルからデータをインポートするための要件については、「一括エクスポートまたは一括インポートのデータの準備 (SQL Server)」をご覧ください。

ログの動作

SQL Server への一括インポートによって実行される行挿入操作がトランザクションログに記録される条件について詳しくは、「一括インポートで最小ログ記録を行うための前提条件」をご覧ください。最小ログ記録は、Azure SQL Database ではサポートされていません。

制限

BULK INSERT でフォーマットファイルを使用する場合、指定できるフィールド数は 1,024 個までです。これは、テーブルに許容される最大列数と同じです。フォーマットファイルを使用して、1,024 個を超えるフィールドが含まれるデータファイルで BULK INSERT を使用すると、BULK INSERT によってエラー 4822 が生成されます。 bcp ユーティリティにはこのような制限がないため、1,024 個を超えるフィールドを含むデータファイルには、フォーマットファイルを使用せずに BULK INSERT を使用するか、bcp コマンドを使用してください。

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

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

実際のデータの読み込みにはさまざまなバッチサイズを試し、最適なサイズを見つける必要があります。バッチサイズにはロールバックによる部分的な影響があることに注意してください。プロセスが失敗して、BULK INSERT を再度使用する場合は、エラーが発生する前に、正常に挿入された行の一部を削除する追加の手動作業が必要になる場合があります。

Azure SQL Database では、大量のデータをインポートする場合、インポートの前にデータベースまたはインスタンスのパフォーマンスレベルを一時的に上げることを検討してください。

セキュリティ

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

ユーザーが SQL Server ログインを使用する場合は、 SQL Server プロセスアカウントのセキュリティプロファイルが使用されます。 SQL Server 認証を使用したログインは、データベースエンジン以外では認証できません。そのため、SQL Server 認証を使用したログインによって BULK INSERT コマンドが開始されると、SQL Server プロセスアカウント (SQL Server データベースエンジンサービスで使用されるアカウント) のセキュリティコンテキストを使用してデータへの接続が行われます。

ソースデータを正しく読み取るには、SQL Server データベースエンジンで使用されるアカウントに対して、ソースデータへのアクセス権を付与する必要があります。これに対して、 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...) を使用した一括データのインポート (SQL Server)」をご覧ください。

Azure Blob Storage からパブリック (匿名アクセス) ではないデータをインポートするときは、MASTER KEY で暗号化された SAS キーに基づいて DATABASE SCOPED CREDENTIAL を作成した後、BULK INSERT コマンドで使用する外部データベースソースを作成します。

または、MANAGED IDENTITY に基づいて DATABASE SCOPED CREDENTIAL を作成し、非パブリックストレージアカウントでのデータアクセスの要求を承認します。 MANAGED IDENTITY を使用する場合、必要な Azure Blob Storage コンテナーのマネージド ID の読み書きのアクセスを提供する、組み込みのストレージ BLOB データ共同作成者の Azure ロールベースのアクセス制御 (RBAC) ロールを追加して、Azure ストレージは、インスタンスのマネージド ID にアクセス許可を付与する必要があります。 Azure SQL Managed Instance には、システム割り当てマネージド ID があり、さらに 1 つ以上のユーザー割り当てマネージド ID がある場合があります。システム割り当てマネージド ID またはユーザー割り当てマネージド ID のいずれかを使って要求を認可できます。承認には、マネージドインスタンスの default ID が使用されます (つまり、ユーザー割り当てマネージド ID が指定されていない場合は、プライマリユーザー割り当てマネージド ID、またはシステム割り当てマネージド ID)。例については、「Azure Blob Storage 内のファイルからデータをインポートする」を参照してください。

重要

マネージド ID は、Azure SQL にのみ適用されます。 SQL Server ではマネージド ID はサポートされていません。

アクセス許可

INSERT 権限および ADMINISTER BULK OPERATIONS 権限が必要です。 Azure SQL Database では、INSERT および ADMINISTER DATABASE BULK OPERATIONS 権限が必要です。 SQL Server on Linux では、ADMINISTER BULK OPERATIONS アクセス許可または bulkadmin ロールはサポートされていません。 SQL Server on Linux に対して一括挿入を実行できるのは、sysadmin だけです。

ただし、次の条件が 1 つまたは複数当てはまる場合は、さらに ALTER TABLE 権限が必要になります。

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

制約の無効化は既定の動作です。制約を明示的に検証するには、CHECK_CONSTRAINTS オプションを使用します。
トリガーが存在する場合に、FIRE_TRIGGER オプションを指定しない。

既定では、トリガーは起動しません。トリガーを明示的に起動するには、FIRE_TRIGGERS オプションを使用します。
KEEPIDENTITY オプションを使用して、データファイルから ID 値をインポートする。

例

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

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

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

重要

Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

B. FIRE_TRIGGERS 引数を使用する

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

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

重要

Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

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

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

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

注意

Microsoft Windows によるテキストファイルの処理方法によって、\n は自動的に \r\n に置き換えられます。

重要

Azure SQL データベースでサポートされるのは、Azure Blob Storage からの読み取りのみです。

D. コードページを指定する

コードページを指定する例を次に示します。

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

重要

Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

E. CSV ファイルからデータをインポートします

次の例は、; をフィールドターミネータ、0x0a を行ターミネータとして使用して、ヘッダー (先頭行) をスキップして CSV ファイルを指定する方法を示しています。

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

次の例は、; をフィールドターミネータ、0x0a を行ターミネータとして使用して、ヘッダー (先頭行) をスキップして UTF-8 形式で (65001 の CODEPAGE を使用して) CSV ファイルを指定する方法を示しています。

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

重要

Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

F. Azure Blob Storage 内のファイルからデータをインポートする

次の例では、Shared Access Signature (SAS) を作成した Azure Blob Storage の場所にある CSV ファイルからデータを読み込む方法を示します。 Azure Blob Storage の場所は外部データソースとして構成されます。これには、ユーザーデータベースのマスターキーにより暗号化された SAS キーを使用したデータベーススコープ資格情報が必要です。

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL = MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

次の例では、BULK INSERT コマンドを使用して、マネージド ID を使用して Azure Blob Storage の場所にある csv ファイルからデータを読み込む方法を示します。 Azure Blob Storage の場所は、外部データソースとして構成されます。

--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
WITH IDENTITY = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

重要

マネージド ID は、Azure SQL にのみ適用されます。 SQL Server ではマネージド ID はサポートされていません。

重要

Azure SQL でサポートされるのは、Azure Blob Storage からの読み取りのみです。

G. Azure Blob Storage 内のファイルからデータをインポートし、エラーファイルを指定する

次の例では、外部データソースとして構成されている Azure Blob Storage の場所に CSV ファイルからデータを読み込み、エラーファイルを指定する方法を示します。 Shared Access Signature を使用したデータベーススコープ資格情報が必要になります。 Azure SQL Database を実行している場合、ERRORFILE オプションと共に ERRORFILE_DATA_SOURCE も指定する必要があります。指定しないと、アクセス許可エラーでインポートが失敗する可能性があります。 ERRORFILE で指定するファイルは、コンテナー内に存在していてはなりません。

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (
         DATA_SOURCE = 'MyAzureInvoices'
         , FORMAT = 'CSV'
         , ERRORFILE = 'MyErrorFile'
         , ERRORFILE_DATA_SOURCE = 'MyAzureInvoices');

資格情報と外部データソースの構成などの BULK INSERT の詳細な例については、「Azure BLOB ストレージのデータに一括アクセスする例」をご覧ください。

その他の例

BULK INSERT のその他の例については、次の記事をご覧ください。

次の方法で共有

BULK INSERT (Transact-SQL)

構文

引数

database_name

schema_name

table_name

FROM 'data_file'

BATCHSIZE = batch_size

CHECK_CONSTRAINTS

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

DATA_SOURCE = 'data_source_name'

ERRORFILE = 'error_file_path'

ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name'

FIRSTROW = first_row

FIRE_TRIGGERS

FORMATFILE_DATA_SOURCE = 'data_source_name'

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

TABLOCK

入力ファイル フォーマットのオプション

FORMAT = 'CSV'

FIELDQUOTE = 'field_quote'

FORMATFILE = 'format_file_path'

FIELDTERMINATOR = 'field_terminator'

ROWTERMINATOR = 'row_terminator'

互換性

データ型

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

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

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

解説

相互運用性

CSV ファイルからデータをインポートします

ログの動作

制限

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

セキュリティ

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

アクセス許可

例

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

B. FIRE_TRIGGERS 引数を使用する

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

D. コード ページを指定する

E. CSV ファイルからデータをインポートします

F. Azure Blob Storage 内のファイルからデータをインポートする

G. Azure Blob Storage 内のファイルからデータをインポートし、エラー ファイルを指定する

その他の例

関連項目

フィードバック

その他のリソース

入力ファイルフォーマットのオプション

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

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

D. コードページを指定する

G. Azure Blob Storage 内のファイルからデータをインポートし、エラーファイルを指定する