OPENROWSET BULK(Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric の SQL データベース

OPENROWSET関数は、1 つまたは複数のファイルからデータを読み取り、その内容を行セットとして返します。 サービスによっては、ファイルが Azure Blob Storage、Azure Data Lake Storage、オンプレミス ディスク、ネットワーク共有などに格納される場合があります。テキスト/CSV、Parquet、JSON 行など、さまざまなファイル形式を読み取ることができます。

OPENROWSET関数は、クエリのFROM句でテーブル名であるかのように参照できます。 SELECTステートメント内のデータを読み取ったり、UPDATE、INSERT、DELETE、MERGE、CTAS、またはCETASステートメント内のターゲット データを更新したりするために使用できます。

• OPENROWSET(BULK) は、外部データ ファイルからデータを読み取る目的で設計されています。
• OPENROWSET BULKを使用しない場合は、別のデータベース エンジンからの読み取り用に設計されています。 詳細については、 OPENROWSET (Transact-SQL) を参照してください。

この記事と OPENROWSET(BULK) で設定される引数は、プラットフォームによって異なります。

• Microsoft Fabric 構文の場合は、 バージョンのドロップダウン リストで [Fabric] を選択します。
• SQL Server、Azure SQL Database、および Azure SQL Managed Instance の構文の場合は、 バージョンのドロップダウン リストでプラットフォームを選択します。

他のプラットフォームでの同様の例の詳細とリンク:

• Azure SQL Database での OPENROWSET の詳細については、「Azure SQL Database を 使用したデータ仮想化」を参照してください。
• Azure SQL Managed Instance での OPENROWSET の詳細については、「Azure SQL Managed Instance を 使用したデータ仮想化」を参照してください。
• Azure Synapse のサーバーレス SQL プールの詳細と例については、「 Azure Synapse Analytics でサーバーレス SQL プールを使用して OPENROWSET を使用する方法を参照してください。
• Azure Synapse の専用 SQL プールでは、 OPENROWSET 関数はサポートされていません。

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、Fabric 内の SQL データベース、Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Arguments

BULK オプションの引数を使用すると、データの読み取りを開始および終了する場所、エラーに対処する方法、およびデータの解釈方法を大幅に制御できます。 たとえば、データ ファイルを varbinary、 varchar、または nvarchar 型の単一行、単一列の行セットとして読み取るように指定できます。 既定の動作については、後の引数の説明を参照してください。

BULK オプションの使用方法については、この記事で後述する「解説」セクションを参照してください。 BULK オプションに必要なアクセス許可については、この記事の後半の「アクセス許可」セクションを参照してください。

一括インポート用にデータを準備する方法については、「 一括エクスポートまたは一括インポート用のデータの準備を参照してください。

バルク『data_file_path』

データが読み取られ、行セットとして返されるデータ ファイルのパスまたは URI。

URI は、Azure Data Lake Storage または Azure Blob Storage を参照できます。 データが読み取られ、行セットとして返されるデータ ファイルの URI。

サポートされているパス形式は次のとおりです。

• <drive letter>:\<file path> ローカル ディスク上のファイルにアクセスするには
• \\<network-share\<file path> ネットワーク共有上のファイルにアクセスするには
• adls://<container>@<storage>.dfs.core.windows.net/<file path> Azure Data Lake Storage にアクセスするには
• abs://<storage>.blob.core.windows.net/<container>/<file path> Azure Blob Storage にアクセスするには
• s3://<ip-address>:<port>/<file path> s3 互換ストレージにアクセスするには

Note

この記事とサポートされている URI パターンは、プラットフォームによって異なります。 Microsoft Fabric Data Warehouse で使用できる URI パターンについては、 バージョンのドロップダウン リストで [Fabric] を選択します。

SQL Server 2017 (14.x) 以降では、 data_file を Azure Blob Storage に格納できます。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

URI には、任意のシーケンスの文字に一致する*文字を含めることができるため、は URI に対してパターンマッチングを行うことができます。 さらに、 /** で終わり、すべてのサブフォルダーを再帰的に走査できます。 SQL Server では、この動作は SQL Server 2022 (16.x) 以降で使用できます。

例えば次が挙げられます。

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

URI で参照できるストレージの種類を次の表に示します。

バージョン	On-premises	Azure Storage	Fabricの OneLake	S3	Google Cloud(GCS)
SQL Server 2017 (14.x)、SQL Server 2019 (15.x)	イエス	イエス	いいえ	いいえ	いいえ
SQL Server 2022 (16.x)	イエス	イエス	いいえ	イエス	いいえ
Azure SQL Database	いいえ	イエス	いいえ	いいえ	いいえ
Azure SQL Managed Instance	いいえ	イエス	いいえ	いいえ	いいえ
Azure Synapse Analytics のサーバーレス SQL プール	いいえ	イエス	イエス	いいえ	いいえ
Microsoft Fabric Warehouse と SQL 分析エンドポイント	いいえ	イエス	イエス	はい、OneLake in Fabricのショートカットを使っています	はい、OneLake in Fabricのショートカットを使っています
Microsoft Fabric の SQL データベース	いいえ	はい、OneLake in Fabricのショートカットを使っています	イエス	はい、OneLake in Fabricのショートカットを使っています	はい、OneLake in Fabricのショートカットを使っています

DATA_SOURCE

DATA_SOURCE は、データ ファイル パスのルートの場所を定義します。 これにより、BULK パスで相対パスを使用できます。 データ ソースは CREATE EXTERNAL DATA SOURCE を使用して作成されます。

ルートの場所に加えて、その場所のファイルへのアクセスに使用できるカスタム資格情報を定義できます。

例えば次が挙げられます。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

ファイル形式のオプション

CODEPAGE

データ ファイル内のデータのコード ページを指定します。 CODEPAGE は、文字値が 127 以上または 32 未満の char、 varchar、または テキスト 列がデータに含まれている場合にのみ関連します。 有効な値は、'ACP'、'OEM'、'RAW'、または 'code_page' です。

CODEPAGE 値	Description
ACP	char、varchar、またはテキスト データ型の列を ANSI/Microsoft Windows コード ページ (ISO 1252) から SQL Server コード ページに変換します。
OEM (既定値)	char、varchar、またはテキスト データ型の列をシステム OEM コード ページから SQL Server コード ページに変換します。
RAW	コード ページの変換は行われません。 これは最も高速なオプションです。
code_page	データ ファイルの文字データのエンコードに使用されているソースのコード ページを示します (例 : 850)。

Important

SQL Server 2016 (13.x) より前のバージョンでは、コード ページ 65001 (UTF-8 エンコード) はサポートされていません。 CODEPAGE は Linux でサポートされているオプションではありません。

Note

フォーマット ファイルの各列に対して照合順序名を指定することをお勧めします (65001 オプションを照合順序/コード ページ仕様よりも優先する場合を除く)。

DATAFILETYPE

OPENROWSET(BULK)が 1 バイト (ASCII、UTF8) またはマルチバイト (UTF16) ファイルの内容を読み取る必要があることを指定します。 有効な値は char と widechar です。

DATAFILETYPE 値	すべてのデータが示す形式
char (既定値)	文字形式。 詳細については、「 文字形式を使用してデータをインポートまたはエクスポートする」を参照してください。
widechar	Unicode 文字。 詳細については、「 Unicode 文字形式を使用したデータのインポートまたはエクスポート」を参照してください。

FORMAT

参照されるファイルの形式を指定します。次に例を示します。

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

有効な値は、バージョンに応じて、'CSV' ( RFC 4180 標準に準拠したコンマ区切り値ファイル)、'PARQUET'、'DELTA' (バージョン 1.0)、および 'JSONL' です。

バージョン	CSV	PARQUET	DELTA	JSONL
SQL Server 2017 (14.x)、SQL Server 2019 (15.x)	イエス	いいえ	いいえ	いいえ
SQL Server 2022 (16.x) 以降のバージョン	イエス	イエス	イエス	いいえ
Azure SQL Database	イエス	イエス	イエス	いいえ
Azure SQL Managed Instance	イエス	イエス	イエス	いいえ
Azure Synapse Analytics のサーバーレス SQL プール	イエス	イエス	イエス	いいえ
Microsoft Fabric Warehouse と SQL 分析エンドポイント	イエス	イエス	いいえ	イエス
Microsoft Fabric の SQL データベース	イエス	イエス	いいえ	いいえ

FORMATFILE

フォーマット ファイルの完全パスを指定します。 SQL Server では、次の 2 種類のフォーマット ファイルがサポートされます: XML と非 XML。

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

フォーマット ファイルは、結果セットの列の型を定義する場合に必要となります。 唯一の例外は、 SINGLE_CLOB、 SINGLE_BLOB、または SINGLE_NCLOB が指定されている場合です。この場合、フォーマット ファイルは必要ありません。

フォーマット ファイルの詳細については、「 フォーマット ファイルを使用してデータを一括インポートする (SQL Server)」を参照してください。

SQL Server 2017 (14.x) 以降では、format_file_path は Azure Blob Storage に格納することができます。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE は、フォーマット ファイル パスのルートの場所を定義します。 FORMATFILE オプションで相対パスを使用できます。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

フォーマット ファイルのデータ ソースは、 CREATE EXTERNAL DATA SOURCE を使用して作成されます。 ルートの場所に加えて、その場所のファイルへのアクセスに使用できるカスタム資格情報を定義できます。

テキスト/CSV オプション

ROWTERMINATOR

char データ ファイルと widechar データ ファイルに使用する行ターミネータを指定します。次に例を示します。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

既定の行ターミネータは、\r\n (改行文字) です。 詳細については、「 フィールドターミネータと行ターミネータを指定する」を参照してください。

FIELDTERMINATOR

char および widechar データ ファイルに使用するフィールド ターミネータを指定します。次に例を示します。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

既定のフィールド ターミネータは , (コンマ) です。 詳細については、「 フィールドターミネータと行ターミネータの指定」を参照してください。 たとえば、タブ区切りのデータをファイルから読み取る場合は、次のようにします。

FIELDQUOTE = 'field_quote'

SQL Server 2017 (14.x) 以降、この引数は、次のニューヨークの例のように、CSV ファイルの引用符文字として使用される文字を指定します。

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

このオプションの値として指定できるのは 1 文字だけです。 指定しない場合、" 標準で定義されている引用符文字として引用符文字 () が使用されます。 FIELDTERMINATOR文字 (コンマなど) はフィールド引用符内に配置でき、FIELDQUOTE文字で囲まれたセルでは通常の文字と見なされます。

たとえば、前のニューヨークのサンプル CSV データセットを読み取るには、 FIELDQUOTE = '"'を使用します。 アドレス フィールドの値は、 " (引用符) 文字内のコンマで複数の値に分割されず、1 つの値として保持されます。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

エラー処理オプション

ERRORFILE = 'file_name'

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

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

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

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

ERRORFILE_DATA_SOURCE_NAME

SQL Server 2017 (14.x) 以降では、この引数は、インポート中に見つかったエラーを含むエラー ファイルの場所を指す名前付き外部データ ソースです。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

詳細については、「 CREATE EXTERNAL DATA SOURCE (Transact-SQL)」を参照してください。

MAXERRORS = maximum_errors

フォーマット ファイルで定義されている構文エラーまたは不適合行の最大数を指定します。これは、 OPENROWSET が例外をスローする前に発生する可能性があります。 MAXERRORSに達するまで、OPENROWSETは不適切な行を無視し、読み込まず、無効な行を 1 つのエラーとしてカウントします。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

maximum_errorsの既定値は 10 です。

Note

MAX_ERRORS は、 CHECK 制約や money およびbigint データ型の変換には適用されません。

データ処理オプション

ファーストロー= first_row

読み込み開始行の行番号を指定します。 既定値は 1 です。 指定したデータ ファイルの最初の行を示します。 行番号は行ターミネータの数をカウントして決定されます。 FIRSTROW は 1 から始まります。

ラストロー = last_row

読み込み終了行の行番号を指定します。 既定値は 0 です。 指定したデータ ファイルの最後の行を示します。

ROWS_PER_BATCH = rows_per_batch

データ ファイル内にあるデータ行の概算数を指定します。 この値は推定値であり、実際の行数の近似値 (1 桁以内) である必要があります。 既定では、 ROWS_PER_BATCH はファイルの特性 (ファイルの数、ファイル サイズ、返されるデータ型のサイズ) に基づいて推定されます。 ROWS_PER_BATCH = 0の指定は、ROWS_PER_BATCHを省略する場合と同じです。 例えば次が挙げられます。

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

ORDER ( { column [ ASC |DESC ] } [ ,... n ] [ UNIQUE ] )

データ ファイル内のデータの並べ替え方法を指定するオプションのヒント。 既定では、一括操作はデータ ファイルが並べ替えられていないことを前提に実行されます。 クエリ オプティマイザーが順序を利用して、より効率的なクエリ プランを生成できる場合、パフォーマンスが向上する可能性があります。 次の一覧は、並べ替えを指定する場合に役立つ可能性がある例を示しています。

• クラスター化インデックスを持ち、クラスター化インデックス キーで行セット データが並べ替えられているテーブルに行を挿入する場合。
• 行セットを別のテーブルに結合するとき、並べ替え列と結合列が一致する場合。
• 並べ替え列で行セット データを集約する場合。
• 並べ替え列と結合列が一致するクエリの FROM 句で、行セットをソース テーブルとして使用します。

UNIQUE

データ ファイルに重複するエントリがないことを指定します。

データ ファイル内の実際の行が指定された順序に従って並べ替えられていない場合、または UNIQUE ヒントが指定され、重複するキーが存在する場合は、エラーが返されます。

列のエイリアスは、 ORDER を使用する場合に必要です。 列エイリアス リストは、 BULK 句によってアクセスされる派生テーブルを参照する必要があります。 ORDER句で指定されている列名は、この列エイリアス リストを参照します。 大きな値の型 (varchar(max)、nvarchar(max)、varbinary(max)、xml) とラージ オブジェクト (LOB) 型 (text、ntext、image) の列は指定できません。

コンテンツ オプション

SINGLE_BLOB

varbinary(max)型の単一行の単一列行セットとしてdata_fileの内容を返します。

Important

SINGLE_BLOBとSINGLE_CLOBではなく、SINGLE_NCLOB オプションのみを使用して XML データをインポートすることをお勧めします。これは、SINGLE_BLOBのみがすべての Windows エンコード変換をサポートするためです。

SINGLE_CLOB

data_fileを ASCII として読み取ることで、現在のデータベースの照合順序を使用して、varchar(max) 型の単一行の単一列行セットとして内容を返します。

SINGLE_NCLOB

data_file Unicode として読み取ることで、現在のデータベースの照合順序を使用して、nvarchar(max) 型の単一行の単一列行セットとして内容を返します。

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Permissions

OPENROWSET 外部データ ソースでは、次のアクセス許可が必要です。

• ADMINISTER DATABASE BULK OPERATIONS または
• ADMINISTER BULK OPERATIONS

次の T-SQL の例では、プリンシパルに ADMINISTER DATABASE BULK OPERATIONS を許可します。

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

ターゲット ストレージ アカウントがプライベートの場合、プリンシパルには、ストレージ BLOB データ閲覧者 ロール (またはそれ以上) もコンテナーまたはストレージ アカウント レベルで割り当てられている必要があります。

Remarks

• FROM と共に使用される SELECT 句では、テーブル名の代わりに OPENROWSET(BULK...) を呼び出すことができます。このとき SELECT の機能に制限はありません。

• OPENROWSET で BULK オプションを使用するには、FROM 句に相関名を指定する必要があります。これは範囲変数または別名とも呼ばれます。 AS <table_alias>を追加しないと、エラー メッセージ 491 が発生します。"from 句の一括行セットに関連付け名を指定する必要があります。"

• 列には別名を指定できます。 列の別名リストが指定されていない場合、フォーマット ファイルには列名が必要です。 次のように、列の別名を指定した場合は、フォーマット ファイルの列名をオーバーライドして使用されます。

  • FROM OPENROWSET(BULK...) AS table_alias
  • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

• SELECT...FROM OPENROWSET(BULK...) ステートメントは、データをテーブルにインポートせずに、ファイル内のデータに対してクエリを直接実行します。

• SELECT...FROM OPENROWSET(BULK...) ステートメントでは、フォーマット ファイルを使用して列名とデータ型を指定することで、一括列のエイリアスを一覧表示できます。

• OPENROWSET(BULK...)をINSERTまたはMERGEステートメントのソース テーブルとして使用すると、データ ファイルからテーブルにデータが一括インポートされます。 詳細については、「 BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートするを参照してください。
• OPENROWSET BULK ステートメントで INSERT オプションを使用する場合、BULK 句はテーブル ヒントをサポートします。 TABLOCK などの通常のテーブル ヒントに加えて、BULK 句では、次の特殊なテーブル ヒントを使用できます: IGNORE_CONSTRAINTS (CHECK および FOREIGN KEY 制約のみ無視します)、IGNORE_TRIGGERS、KEEPDEFAULTS、KEEPIDENTITY。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
• INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントの使用方法については、「データの一括インポートと一括エクスポート (SQL Server)」をご覧ください。 一括インポートによって実行される行挿入操作がトランザクション ログに記録される条件について詳しくは、「 一括インポートで最小ログ記録を行うための前提条件」をご覧ください。
• 完全復旧モデルでデータをインポートするために使用した場合、 OPENROWSET (BULK ...) ではログ記録は最適化されません。

Note

OPENROWSETを使用する場合は、SQL Server が偽装をどのように処理するかを理解することが重要です。 セキュリティに関する考慮事項については、「 BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートするを参照してください。

SQLCHAR、SQLNCHAR、または SQLBINARY データの一括インポート

OPENROWSET(BULK...) 指定しない場合、 SQLCHAR、 SQLNCHAR、または SQLBINARY データの最大長が 8,000 バイトを超えないと想定します。 インポートするデータが、8,000 バイトを超える varchar(max)、 nvarchar(max)、または varbinary(max) オブジェクトを含む LOB データ フィールドにある場合は、データ フィールドの最大長を定義する XML フォーマット ファイルを使用する必要があります。 最大の長さを指定するには、フォーマット ファイルを編集して MAX_LENGTH 属性を宣言します。

Note

自動的に生成されるフォーマット・ファイルは、LOB フィールドの長さまたは最大長を指定しません。 ただし、手作業でフォーマット ファイルを編集して長さまたは最大の長さを指定できます。

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

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

データの種類	Effect
SQLCHAR または SQLVARYCHAR	データは、クライアント コード ページまたは照合順序によって暗黙的に示されるコード ページで送信されます。
SQLNCHAR または SQLNVARCHAR	データは Unicode として送られます。
SQLBINARY または SQLVARYBIN	データは変換なしで送られます。

ファイル メタデータ関数

時には、結果セット内の特定の行に対応するファイルやフォルダのソースを知る必要があるかもしれません。

関数 filepath や filename を使ってファイル名や結果セット内のパスを返すことができます。 または、ファイル名やフォルダのパスに基づいてデータをフィルタリングすることもできます。 以下のセクションでは、サンプルに沿って簡単な説明を掲載します。

ファイル名関数

この関数は、行の元となるファイル名を返します。

返却データタイプは nvarchar(1024)です。 最適なパフォーマンスのために、ファイル名関数の結果を適切なデータ型に必ずキャストしてください。 キャラクターデータ型を使う場合は、適切な長さを使うことを確認してください。

以下のサンプルは2017年後半のNYCイエロータクシーのデータファイルを読み取り、ファイルあたりの乗車回数を返します。 クエリの OPENROWSET 部分は、どのファイルを読み取るかを指定します。

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

以下の例は、filename()節でWHEREを使って読みたいファイルをフィルタリングする方法を示しています。 クエリの OPENROWSET 部分でフォルダ全体にアクセスし、 WHERE 節でファイルをフィルタリングします。

結果は前の例と同じになります。

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

ファイルパス関数

この関数は完全なパスまたはその一部を返します:

• パラメータなしで呼び出すと、行が発信する完全なファイルパスを返します。
• パラメータで呼び出すと、パラメータで指定された位置のワイルドカードと一致するパスの一部を返します。 例えば、パラメータ値1は最初のワイルドカードと一致するパスの一部を返します。

返却データタイプは nvarchar(1024)です。 最適なパフォーマンスを得るために、関数の結果 filepath 必ず適切なデータ型にキャストしてください。 キャラクターデータ型を使う場合は、適切な長さを使うことを確認してください。

以下のサンプルは、2017年最後の3か月間のNYCイエロータクシーのデータファイルを読み取っています。 ファイルパスごとの乗車回数を返します。 クエリの OPENROWSET 部分は、どのファイルを読み取るかを指定します。

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

以下の例は、filepath()節でWHEREを使って読みたいファイルをフィルタリングする方法を示しています。

クエリの OPENROWSET 部分でワイルドカードを使い、 WHERE 節でファイルをフィルタリングできます。 結果は前の例と同じになります。

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

このセクションでは、 OPENROWSET BULK 構文の使用方法を示す一般的な例を示します。

A. OPENROWSET を使用して varbinary(max) 列にファイル データを BULK INSERT する

適用対象: SQL Server のみ。

次の例では、デモンストレーション用の小さなテーブルを作成し、Text1.txt ルート ディレクトリにある C: という名前のファイルから varbinary(max) 列にファイル データを挿入します。

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. フォーマット ファイルで OPENROWSET BULK プロバイダーを使用してテキスト ファイルから行を取得する

適用対象: SQL Server のみ。

次の例では、フォーマット ファイルを使用して、タブ区切りのテキスト ファイル values.txt から行を取得します。このテキスト ファイルには次のデータが含まれます。

1     Data Item 1
2     Data Item 2
3     Data Item 3

フォーマット ファイル values.fmt では、values.txt の列が次のように表されています。

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

このクエリは、そのデータを取得します。

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. フォーマット ファイルとコード ページを指定する

適用対象: SQL Server のみ。

次の例は、フォーマット ファイルとコード ページの両方のオプションを同時に使用する方法を示しています。

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. フォーマット ファイルを使用して CSV ファイルからデータにアクセスする

適用対象: SQL Server 2017 (14.x) 以降のバージョンのみ。

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. フォーマット ファイルなしで CSV ファイルからデータにアクセスする

適用対象: SQL Server のみ。

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Important

ODBC ドライバーは 64 ビットである必要があります。 Windows で ODBC データ ソースへの接続 (SQL Server インポートおよびエクスポート ウィザード) アプリケーションの [ドライバー] タブを開き、これを確認します。 64 ビット バージョンのMicrosoft Text Driver (*.txt, *.csv)では動作しない 32 ビットのsqlservr.exeがあります。

F. Azure Blob Storage に格納されているファイルからデータにアクセスする

適用対象: SQL Server 2017 (14.x) 以降のバージョンのみ。

SQL Server 2017 (14.x) 以降のバージョンでは、次の例では、Azure ストレージ アカウント内のコンテナーと、共有アクセス署名用に作成されたデータベース スコープ資格情報を指す外部データ ソースを使用しています。

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

資格情報と外部データ ソースの構成を含む完全な OPENROWSET 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

G. Azure Blob Storage に格納されているファイルからテーブルにインポートする

次の例では、 OPENROWSET コマンドを使用して、SAS キーを作成した 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 = '<password>';
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***************';

-- Make sure that you don't have a leading ? in the 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/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. 外部ソースにマネージド ID を使う

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

次の例では、マネージド ID を使って資格情報を作成し、外部ソースを作成し、外部ソース上でホストされている CSV からデータを読み込みます。

まず資格情報を作成し、BLOB ストレージを外部ソースとして指定します。

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

次に、BLOB ストレージ上でホストされている CSV ファイルからデータを読み込みます。

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. OPENROWSET を使用して、S3 互換オブジェクト ストレージを使用して複数の Parquet ファイルにアクセスする

適用対象: SQL Server 2022 (16.x) 以降のバージョン。

次の例では、S3 互換のオブジェクト ストレージに格納されているすべての場所から複数の Parquet ファイルにアクセスします。

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. OPENROWSET を使用して Azure Data Lake Gen2 から複数の Delta テーブルにアクセスする

適用対象: SQL Server 2022 (16.x) 以降のバージョン。

この例では、データ テーブル コンテナーの名前は Contoso で、Azure Data Lake Gen2 ストレージ アカウントにあります。

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. OPENROWSET を使用してパブリック匿名データセットのクエリを実行する

次の例では、公開されている NYC 黄色のタクシー乗車レコードのオープン データ セットを使用します。

最初にデータ ソースを作成します。

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

名前パターンに一致するフォルダー内 .parquet 拡張子を持つすべてのファイルに対してクエリを実行します。

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

その他の例

OPENROWSET(BULK...)の使用を示すその他の例については、次の記事を参照してください。

• データの一括インポートと一括エクスポート (SQL Server)
• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
• データの一括インポート時の ID 値の保持 (SQL Server)
• 一括インポート中の NULL または既定値の保持 (SQL Server)
• データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
• 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)
• Azure SQL Managed Instances で OPENROWSET を使用してデータ ソースにクエリを実行する
• フィールドターミネータと行ターミネータを指定する (SQL Server)

関連コンテンツ

• 削除(Transact-SQL)
• FROM 句と JOIN、APPLY、PIVOT (Transact-SQL)
• 挿入(Transact-SQL)
• OPENDATASOURCE(Transact-SQL)
• OPENQUERY(Transact-SQL)
• SELECT(Transact-SQL)
• 更新(Transact-SQL)
• ここで(Transact-SQL)