OPENROWSET (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
OLE DB データ ソースからリモート データへのアクセスに必要な、すべての接続情報をインクルードします。 このメソッドは、リンク サーバー内のテーブルにアクセスする代わりに、OLE DB を使用してリモート データに接続しアクセスするアドホック メソッドです。 OLE DB データ ソースをより頻繁に参照する場合は、代わりにリンク サーバーを使用してください。 詳しくは、「リンク サーバー (データベース エンジン)」を参照してください。 OPENROWSET
関数は、クエリのFROM
句でテーブル名であるかのように参照できます。 OPENROWSET
関数は、INSERT
、UPDATE
、または DELETE
ステートメントのターゲット テーブルとしても参照できます。ただしこれは OLE DB プロバイダーの機能により制限されます。 クエリでは複数の結果セットが返される場合がありますが、OPENROWSET
では最初の 1 つだけが返されます。
OPENROWSET
では、組み込みの BULK
プロバイダーによる一括操作もサポートされ、ファイルのデータを行セットとして読み取り、返すことができます。
この記事の多くの例は、SQL Server にのみ適用されます。 他のプラットフォームでの同様の例の詳細とリンク:
- Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
- Azure SQL Managed Instance の例については、OPENROWSET を使用した Query データ ソースを参照してください。
- Azure Synapse のサーバーレス SQL プールの詳細と例については、「 Azure Synapse Analytics でサーバーレス SQL プールを使用して OPENROWSET を使用する方法を参照してください。
- Azure Synapse の専用 SQL プールでは、
OPENROWSET
関数はサポートされていません。
構文
OPENROWSET
構文は、外部データ ソースのクエリに使用されます。
OPENROWSET
( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' }
)
OPENROWSET(BULK)
構文は、外部ファイルを読み取るために使用されます。
OPENROWSET( BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)
<bulk_options> ::=
[ , DATASOURCE = 'data_source_name' ]
-- bulk_options related to input file format
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
[ , FIELDQUOTE = 'quote_characters' ]
[ , FORMATFILE = 'format_file_path' ]
[ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ERRORFILE = 'file_name' ]
[ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ]
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
共通の引数
'provider_name'
レジストリで指定された OLE DB プロバイダーのフレンドリ名 (または PROGID
) を表す文字列。 provider_name 既定値はありません。 プロバイダー名の例としては、Microsoft.Jet.OLEDB.4.0
、SQLNCLI
、MSDASQL
があります。
'datasource'
特定の OLE DB データ ソースに対応する文字列定数。 datasource は、プロバイダーを初期化するためにプロバイダーのIDBProperties
インターフェイスに渡されるDBPROP_INIT_DATASOURCE
プロパティです。 通常、この文字列には、データベース ファイルの名前、データベース サーバーの名前、またはデータベースまたはデータベースを検索するためにプロバイダーが認識する名前が含まれます。
データ ソースには、Microsoft.Jet.OLEDB.4.0
プロバイダーの場合はファイル パス C:\SAMPLES\Northwind.mdb'
、SQLNCLI
プロバイダーの場合は接続文字列 Server=Seattle1;Trusted_Connection=yes;
を指定できます。
'user_id'
指定した OLE DB プロバイダーに渡されるユーザー名を表す文字列定数。 user_id 接続のセキュリティ コンテキストを指定し、プロバイダーを初期化する DBPROP_AUTH_USERID
プロパティとして渡されます。 user_id は Microsoft Windows ログイン名にすることはできません。
'password'
OLE DB プロバイダーに渡すユーザー パスワードを表す文字列定数。 password は、プロバイダーの初期化時に DBPROP_AUTH_PASSWORD
プロパティとして渡されます。 パスワード は Microsoft Windows パスワードにすることはできません。
SELECT a.* FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\SAMPLES\Northwind.mdb';
'admin';
'password',
Customers
) AS a;
'provider_string'
OLE DB プロバイダーを初期化するためにDBPROP_INIT_PROVIDERSTRING
プロパティとして渡されるプロバイダー固有の接続文字列。 provider_string 通常、プロバイダーの初期化に必要なすべての接続情報をカプセル化します。 SQL Server Native Client OLE DB プロバイダーが認識するキーワードの一覧については、「 初期化と承認のプロパティ (ネイティブ クライアント OLE DB プロバイダー)」を参照してください。
SELECT d.* FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
Department
) AS d;
<table_or_view>
OPENROWSET
で読み取る必要のあるデータを含むリモート テーブルまたはビュー。 次の構成要素を持つ 3 つの部分から成る名前のオブジェクトにすることができます。
- catalog (省略可能) - 指定したオブジェクトが存在するカタログまたはデータベースの名前。
- schema (省略可能) - 指定したオブジェクトのスキーマまたはオブジェクト所有者の名前。
- object - 操作するオブジェクトを一意に識別するオブジェクト名。
SELECT d.* FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
AdventureWorks2022.HumanResources.Department
) AS d;
'query'
プロバイダーに送信され、プロバイダーによって実行される文字列定数。 SQL Server のローカル インスタンスでは、このクエリは処理されませんが、プロバイダーによって返されたクエリ結果 (パススルー クエリ) が処理されます。 パススルー クエリは、テーブル名を使用して表形式データを使用できないプロバイダーで使用する場合に便利です。ただし、コマンド言語でのみ使用できます。 パススルー クエリは、クエリ プロバイダーが OLE DB をサポートしていれば、リモート サーバーでサポートされてコマンド オブジェクトとその必須インターフェイス。 詳細については、「 SQL Server Native Client (OLE DB) インターフェイス」を参照してください。
SELECT a.*
FROM OPENROWSET(
'SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;
BULK 引数
BULK
行セット プロバイダーを使用して、ファイルからデータを読み取るOPENROWSET
します。 SQL Server では、 OPENROWSET
は、ターゲット テーブルにデータを読み込まずにデータ ファイルから読み取ることができます。 これにより、基本的なSELECT
ステートメントでOPENROWSET
を使用できます。
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
BULK
オプションの引数を使用すると、データの読み取りを開始および終了する場所、エラーに対処する方法、およびデータの解釈方法を大幅に制御できます。 たとえば、データ ファイルを、 varbinary、 varchar、または nvarchar 型の単一行の単一列行セットとして読み取るように指定できます。 既定の動作については、後の引数の説明を参照してください。
BULK
オプションの使用方法については、この記事で後述する「Remarks」セクションを参照してください。 BULK
オプションに必要なアクセス許可については、この記事の後半の「Permissions」セクションを参照してください。
Note
完全復旧モデルでデータをインポートするために使用した場合、 OPENROWSET (BULK ...)
ではログ記録は最適化されません。
一括インポート用にデータを準備する方法については、「 一括エクスポートまたは一括インポート用のデータの準備を参照してください。
BULK 'data_file'
ターゲット テーブルにデータをコピーするデータ ファイルの完全パス。
SELECT * FROM OPENROWSET(
BULK 'C:\DATA\inv-2017-01-19.csv',
SINGLE_CLOB
) AS DATA;
SQL Server 2017 (14.x) 以降では、data_file は Azure Blob Storage に格納することができます。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
BULK エラー処理オプション
ERRORFILE = 'file_name'
形式エラーがあり、OLE DB 行セットに変換できない行を収集するときに使用するファイルを指定します。 該当する行は、データ ファイルからこのエラー ファイルに "そのまま" コピーされます。
エラー ファイルはコマンドの実行開始時に作成されます。 ファイルが既に存在する場合は、エラーが発生します。 さらに、拡張子 .ERROR.txt の制御ファイルが作成されます。 このファイルにはエラー ファイルの各行の参照と、エラーの診断が含まれています。 エラーが修正されると、データを読み込むことができます。
SQL Server 2017 (14.x) 以降では、error_file_path
は Azure Blob Storage に格納することができます。
ERRORFILE_DATA_SOURCE_NAME
SQL Server 2017 (14.x) 以降では、この引数は、インポート中に見つかったエラーを含むエラー ファイルの Azure Blob Storage の場所を指す名前付き外部データ ソースです。 外部データ ソースは、TYPE = BLOB_STORAGE
を使って作成する必要があります。 詳しくは、「CREATE EXTERNAL DATA SOURCE」をご覧ください。
MAXERRORS = maximum_errors
フォーマット ファイルで定義されている構文エラーまたは不適合行の最大数を指定します。これは、 OPENROWSET
が例外をスローする前に発生する可能性があります。 MAXERRORS
に達するまで、OPENROWSET
は不適切な行を無視し、読み込まず、無効な行を 1 つのエラーとしてカウントします。
既定の maximum_errors は 10 です。
Note
MAX_ERRORS
は、 CHECK
制約や、 money および bigint データ型の変換には適用されません。
BULK データ処理オプション
FIRSTROW = first_row
読み込み開始行の行番号を指定します。 既定値は 1 です。 指定したデータ ファイルの最初の行を示します。 行番号は行ターミネータの数をカウントして決定されます。 FIRSTROW
は 1 から始まります。
LASTROW = last_row
読み込み終了行の行番号を指定します。 既定値は 0 です。 指定したデータ ファイルの最後の行を示します。
ROWS_PER_BATCH = rows_per_batch
データ ファイル内にあるデータ行の概算数を指定します。 この値は実際の行数と同じ次数にする必要があります。
OPENROWSET
では、常にデータ ファイルが単一のバッチとしてインポートされますが、 rows_per_batch に 0 より大きい値 (> 0) を指定した場合、クエリ プロセッサでは、クエリ プランのリソース割り当てのヒントとして rows_per_batch の値が使用されます。
既定では、 ROWS_PER_BATCH
は不明です。 ROWS_PER_BATCH = 0
の指定は、ROWS_PER_BATCH
を省略する場合と同じです。
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
data_file の内容を、varbinary(max) 型の単一行、単一列の行セットとして返します。
重要
SINGLE_CLOB
とSINGLE_NCLOB
ではなく、SINGLE_BLOB
オプションのみを使用して 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;
BULK 入力ファイル形式のオプション
CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
データ ファイル内のデータのコード ページを指定します。 CODEPAGE
は、 char、 varchar、または text 文字値が 127 を超える列または 32 未満の列がデータに含まれている場合にのみ関連します。
重要
CODEPAGE
は Linux でサポートされているオプションではありません。
Note
フォーマット ファイルの各列に対して照合順序名を指定することをお勧めします (65001 オプションを照合順序/コード ページ仕様よりも優先する場合を除く)。
CODEPAGE の値 | 説明 |
---|---|
ACP |
char、varchar、または text データ型の列を、ANSI/Microsoft Windows コード ページ (ISO 1252) から SQL Server コード ページに変換します。 |
OEM (既定) |
char、varchar、または text データ型の列を、システムの OEM コード ページから SQL Server コード ページに変換します。 |
RAW |
コード ページの変換は行われません。 これは最も高速なオプションです。 |
code_page |
データ ファイルの文字データのエンコードに使用されているソースのコード ページを示します (例 : 850)。 重要 SQL Server 2016 (13.x) より前のバージョンでは、コード ページ 65001 (UTF-8 エンコード) はサポートされていません。 |
FORMAT = { 'CSV' |'PARQUET' |'DELTA' }
SQL Server 2017 (14.x) 以降では、この引数は、 RFC 4180 標準に準拠するコンマ区切り値ファイルを指定します。
SQL Server 2022 (16.x) 以降では、Parquet 形式と Delta 形式の両方がサポートされています。
SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
FORMATFILE = N'D:\XChange\test-csv.fmt',
FIRSTROW=2,
FORMAT='CSV') AS cars;
FORMATFILE = 'format_file_path'
フォーマット ファイルの完全パスを指定します。 SQL Server では、次の 2 種類のフォーマット ファイルがサポートされます: XML と非 XML。
フォーマット ファイルは、結果セットの列の型を定義する場合に必要となります。 唯一の例外は、 SINGLE_CLOB
、 SINGLE_BLOB
、または SINGLE_NCLOB
が指定されている場合です。この場合、フォーマット ファイルは必要ありません。
フォーマット ファイルの詳細については、「 フォーマット ファイルを使用してデータを一括インポートする (SQL Server)」を参照してください。
SQL Server 2017 (14.x) 以降では、format_file_path は Azure Blob Storage に格納することができます。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。
FIELDQUOTE = 'field_quote'
SQL Server 2017 (14.x) 以降では、この引数は CSV ファイルの引用符文字として使用される文字を指定します。 指定しない場合、見積もり文字 ("
) は、 RFC 4180 標準で定義されている引用符文字として使用されます。
解説
OPENROWSET
は、OLE DB データ ソースからリモート データにアクセスするときに使用できます。ただしこの場合、指定したプロバイダーに対して DisallowAdhocAccess レジストリ オプションが明示的に 0 に設定されており、Ad Hoc Distributed Queries 詳細構成オプションが有効になっている必要があります。 これらのオプションが設定されていない場合、既定の動作ではアドホック アクセスは許可されません。
リモート OLE DB データ ソースにアクセスすると、クライアントがクエリ対象のサーバーに接続されているサーバーから、信頼された接続のログイン ID が自動的に委任されることはありません。 したがって、認証の委任を構成する必要があります。
指定したデータ ソースにおいて、OLE DB プロバイダーが複数のカタログとスキーマをサポートする場合は、カタログ名とスキーマ名を指定する必要があります。
OPENROWSET
では、引数の変数は受け入れられません。
FROM
句での OPENDATASOURCE
、OPENQUERY
、または OPENROWSET
の呼び出しは、更新のターゲットとして使用されるこれらの関数の呼び出しとは別に評価されます。これは、両方の呼び出しに同じ引数が指定されている場合にも当てはまります。 特に、いずれか一方の呼び出しの結果に適用されるフィルター条件または結合条件は、もう一方の結果に影響しません。
BULK オプションで OPENROWSET を使用する
次の Transact-SQL 拡張機能では、 OPENROWSET(BULK...)
関数がサポートされています。
SELECT
と共に使用されるFROM
句では、テーブル名の代わりにOPENROWSET(BULK...)
を呼び出すことができます。このときSELECT
の機能に制限はありません。OPENROWSET
でBULK
オプションを使用するには、FROM
句に相関名を指定する必要があります。これは範囲変数または別名とも呼ばれます。 列には別名を指定できます。 列の別名リストが指定されていない場合、フォーマット ファイルには列名が必要です。 次のように、列の別名を指定した場合は、フォーマット ファイルの列名をオーバーライドして使用されます。FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
重要
AS <table_alias>
への追加に失敗すると次のエラーが発生します:Msg 491, Level 16, State 1, Line 20 FROM 句の一括行セットには相関名を指定してください。SELECT...FROM OPENROWSET(BULK...)
ステートメントは、データをテーブルにインポートせずに、ファイル内のデータに対してクエリを直接実行します。 また、SELECT...FROM OPENROWSET(BULK...)
ステートメントでフォーマット ファイルを使用して列名やデータ型を指定すると、一括列の別名を列挙することもできます。INSERT
ステートメントまたはMERGE
ステートメント内でソース テーブルとしてOPENROWSET(BULK...)
を使用すると、データ ファイルから SQL Server テーブルにデータが一括インポートされます。 詳細については、「 BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートするを参照してください。INSERT
ステートメントでOPENROWSET BULK
オプションを使用する場合、BULK
句はテーブル ヒントをサポートします。TABLOCK
などの通常のテーブル ヒントに加えて、BULK
句では、次の特殊なテーブル ヒントを使用できます:IGNORE_CONSTRAINTS
(CHECK
およびFOREIGN KEY
制約のみ無視します)、IGNORE_TRIGGERS
、KEEPDEFAULTS
、KEEPIDENTITY
。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。INSERT...SELECT * FROM OPENROWSET(BULK...)
ステートメントの使用方法については、「データの一括インポートと一括エクスポート (SQL Server)」をご覧ください。 一括インポートによって実行される行挿入操作がトランザクション ログに記録される条件について詳しくは、「 一括インポートで最小ログ記録を行うための前提条件」をご覧ください。
Note
OPENROWSET
を使用する場合は、SQL Server が偽装をどのように処理するかを理解することが重要です。 セキュリティに関する考慮事項については、「 BULK INSERT または OPENROWSET(BULK...) を使用して SQL Server にデータをインポートするを参照してください。
SQLCHAR、SQLNCHAR、または SQLBINARY データの一括インポート
OPENROWSET(BULK...)
指定しない場合、 SQLCHAR
、 SQLNCHAR
、または SQLBINARY
データの最大長が 8,000 バイトを超えないと想定します。 インポートするデータが、 varchar(max)、 nvarchar(max)、または varbinary(max) オブジェクトが 8,000 バイトを超える LOB データ フィールドにある場合は、データ フィールドの最大長を定義する XML フォーマット ファイルを使用する必要があります。 最大の長さを指定するには、フォーマット ファイルを編集して MAX_LENGTH 属性を宣言します。
Note
自動的に生成されるフォーマット・ファイルは、LOB フィールドの長さまたは最大長を指定しません。 ただし、手作業でフォーマット ファイルを編集して長さまたは最大の長さを指定できます。
SQLXML ドキュメントの一括エクスポートまたは一括インポート
SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には次のいずれかを使用します。
データ型 | 結果 |
---|---|
SQLCHAR または SQLVARYCHAR |
データは、クライアント コード ページまたは照合順序によって暗黙的に示されるコード ページで送信されます。 |
SQLNCHAR または SQLNVARCHAR |
データは Unicode として送られます。 |
SQLBINARY または SQLVARYBIN |
データは変換なしで送られます。 |
アクセス許可
OPENROWSET
権限は、OLE DB プロバイダーに渡されるユーザー名の権限によって決まります。 BULK
オプションを使用するには、ADMINISTER BULK OPERATIONS
または ADMINISTER DATABASE BULK OPERATIONS
のアクセス許可が必要です。
例
このセクションでは、OPENROWSET の使用方法を示す一般的な例を示します。
A. SELECT と SQL Server Native Client OLE DB Provider で OPENROWSET を使用する
適用対象: SQL Server のみ。
SQL Server Native Client (SNAC と略されることがよくあります) は、SQL Server 2022 (16.x) と SQL Server Management Studio 19 (SSMS) から削除されました。 SQL Server Native Client OLE DB プロバイダー (SQLNCLI または SQLNCLI11) とレガシ Microsoft OLE DB Provider for SQL Server (SQLOLEDB) はどちらも、新しい開発には推奨されません。 今後は、新しい Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server に切り替えてください。
次の例では、SQL Server Native Client OLE DB プロバイダーを使用して、リモート サーバー HumanResources.Department
のデータベース AdventureWorks2022
のテーブル Seattle1
にアクセスします (SQLNCLI を使用すると、SQL Server により最新バージョンの SQL Server Native Client OLE DB プロバイダーにリダイレクトされます)。SELECT
ステートメントは、返す行セットの定義に使用します。 プロバイダーの文字列には、Server
と Trusted_Connection
キーワードが含まれます。 これらのキーワードは、SQL Server Native Client OLE DB プロバイダーによって認識されます。
SELECT a.*
FROM OPENROWSET(
'SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2022.HumanResources.Department
ORDER BY GroupName, Name'
) AS a;
B. Microsoft OLE DB Provider for Jet を使用する
適用対象: SQL Server のみ。
次の例では、Microsoft OLE DB Provider for Jet を介して、Microsoft Access Northwind
データベース内のテーブル Customers
にアクセスします。
Note
この例では、Microsoft Access がインストールされていることを前提としています。 この例を実行するには、 Northwind
データベースをインストールする必要があります。
SELECT CustomerID, CompanyName
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',
Customers
);
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
C: INNER JOIN で OPENROWSET と別のテーブルを使用する
適用対象: SQL Server のみ。
次の例では、ローカル インスタンスの SQL Server Northwind
データベース内の Customers
テーブル、および同じコンピューター上に格納されている Access Northwind
データベースの Orders
テーブルから、すべてのデータを選択します。
注意
この例では、Access がインストールされていることを前提としています。 この例を実行するには、 Northwind
データベースをインストールする必要があります。
USE Northwind;
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
Orders) AS o
ON c.CustomerID = o.CustomerID;
重要
Azure SQL データベース でサポートされるのは、Azure Blob Storage からの読み取りのみです。
D. OPENROWSET を使用して varbinary(max) 列にファイル データを BULK INSERT する
適用対象: SQL Server のみ。
次の例では、デモンストレーション用の小さなテーブルを作成し、C:
ルート ディレクトリにある Text1.txt
という名前のファイルから 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
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
E. フォーマット ファイルで 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;
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
F. フォーマット ファイルとコード ページを指定する
適用対象: SQL Server のみ。
次の例は、フォーマット ファイルとコード ページの両方のオプションを同時に使用する方法を示しています。
INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
BULK N'D:\data.csv',
FORMATFILE = 'D:\format_no_collation.txt',
CODEPAGE = '65001'
) AS a;
G. フォーマット ファイルを使用して 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;
重要
Azure SQL データベース でサポートされるのは、Azure Blob Storage からの読み取りのみです。
H. フォーマット ファイルなしで CSV ファイルからデータにアクセスする
適用対象: SQL Server のみ。
SELECT * FROM OPENROWSET(
BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\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'
);
重要
ODBC ドライバーは 64 ビットである必要があります。 これを確認するには、windows の Drivers Connect to an ODBC Data Source (SQL Server Import and Export Wizard) アプリケーションの [Drivers] タブを開きます。 64 ビット バージョンのsqlservr.exe
では動作しない 32 ビットのMicrosoft Text Driver (*.txt, *.csv)
があります。
I. 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 のデータへの一括アクセスの例を参照してください。
J. 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 = '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***************';
-- 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;
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
K. 外部ソースにマネージド ID を使う
次の例では、マネージド ID を使って資格情報を作成し、外部ソースを作成し、外部ソース上でホストされている CSV からデータを読み込みます。
まず資格情報を作成し、BLOB ストレージを外部ソースとして指定します。
CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';
CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://****************.blob.core.windows.net/curriculum',
CREDENTIAL = sampletestcred
);
次に、BLOB ストレージ上でホストされている CSV ファイルからデータを読み込みます。
SELECT * FROM OPENROWSET(
BULK 'Test - Copy.csv',
DATA_SOURCE = 'SampleSource',
SINGLE_CLOB
) as test;
重要
Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。
L. 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;
M. OPENROWSET を使用して Azure Data Lake Gen2 から複数のデルタ ファイルにアクセスする
適用対象: 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;
その他の例
INSERT...SELECT * FROM OPENROWSET(BULK...)
の使用を示すその他の例については、次の記事を参照してください。
- XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
- データの一括インポート時の ID 値の保持 (SQL Server)
- 一括インポート中の NULL または既定値の保持 (SQL Server)
- データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
- 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
- フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
- フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
- フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)
- Azure SQL Managed Instances で OPENROWSET を使用してデータ ソースにクエリを実行する
関連するコンテンツ
- DELETE (Transact-SQL)
- FROM 句と JOIN、APPLY、PIVOT (Transact-SQL)
- データの一括インポートと一括エクスポート (SQL Server)
- INSERT (Transact-SQL)
- OPENDATASOURCE (Transact-SQL)
- OPENQUERY (Transact-SQL)
- SELECT (Transact-SQL)
- sp_addlinkedserver (Transact-SQL)
- sp_serveroption (Transact-SQL)
- UPDATE (Transact-SQL)
- WHERE (Transact-SQL)