OPENROWSET (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

OLE DB データ ソースからリモート データへのアクセスに必要な、すべての接続情報をインクルードします。 このメソッドは、リンク サーバー内のテーブルにアクセスする代わりに、OLE DB を使用してリモート データに接続しアクセスするアドホック メソッドです。 OLE DB データ ソースをより頻繁に参照する場合は、代わりにリンク サーバーを使用してください。 詳しくは、「リンク サーバー (データベース エンジン)」を参照してください。 OPENROWSET 関数は、テーブル名と同じように、クエリの FROM 句で参照できます。 OPENROWSET 関数は、INSERT、UPDATE、または DELETE ステートメントのターゲット テーブルとしても参照できます。ただしこれは OLE DB プロバイダーの機能により制限されます。 クエリでは複数の結果セットが返される場合がありますが、OPENROWSET では最初の 1 つだけが返されます。

OPENROWSET では、組み込みの BULK プロバイダーによる一括操作もサポートされ、ファイルのデータを行セットとして読み取り、返すことができます。

注意

この記事は、Azure Synapse Analytics には当てはまりません。

• Azure Synapse のサーバーレス SQL プールで OPENROWSET を使う方法については、「Azure Synapse Analytics でサーバーレス SQL プールを使う際の OPENROWSET の使用方法」をご覧ください。
• OPENROWSET 関数は、Azure Synapse の専用 SQL プールではサポートされていません。

Transact-SQL 構文表記規則

構文

OPENROWSET
( { 'provider_name' 
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {   <table_or_view> | 'query' }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<table_or_view> ::= [ catalog. ] [ schema. ] object

<bulk_options> ::=

   [ , DATASOURCE = 'data_source_name' ]

   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]
   [ , MAXERRORS = maximum_errors ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]
  
   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

Note

SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

'provider_name'

レジストリの OLE DB プロバイダーの表示名 (または PROGID) を表す文字列を指定します。 provider_name 既定値はありません。 プロバイダー名の例としては、Microsoft.Jet.OLEDB.4.0、SQLNCLI、MSDASQL があります。

'datasource'

特定の OLE DB データ ソースに対応する文字列定数を指定します。 datasource は DBPROP_INIT_DATASOURCE のプロパティで、プロバイダーの IDBProperties インターフェイスに渡され、プロバイダーの初期化に使用されます。 一般的に、この文字列にはデータベース ファイルの名前、データベース サーバーの名前、プロバイダーがデータベースを検索する際に認識する名前のいずれかを指定します。 データ ソースには、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 プロパティとして引き渡され、プロバイダーの初期化に使用されます。 password を Microsoft Windows のパスワードとすることはできません。

SELECT a.*
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                   'C:\SAMPLES\Northwind.mdb';
                   'admin';
                   'password',
                   Customers) AS a;

'provider_string'

プロバイダー固有の接続文字列を指定します。DBPROP_INIT_PROVIDERSTRING プロパティとして渡され、プロバイダーの初期化に使用されます。 provider_string 通常、プロバイダーの初期化に必要なすべての接続情報をカプセル化します。 SQL Server Native Client 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;',
                 AdventureWorks2012.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 AdventureWorks2012.HumanResources.Department') AS a;

BULK

ファイルからのデータ読み取りに OPENROWSET の BULK 行セット プロバイダーを使用します。 SQL Server では、OPENROWSET を使用すると、データを対象テーブルに読み込むことなくデータ ファイルからの読み取りができます。 このため、OPENROWSET は簡単な SELECT ステートメントで使用できます。

重要

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

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

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

Note

OPENROWSET (BULK ...) を完全復旧モデルでデータのインポートに使用する場合、ログ記録は最適化されません。

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

BULK 'data_file'

データを対象テーブルにコピーするデータ ファイルの完全なパスを指定します。

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB) AS DATA;

適用対象: SQL Server 2017 (14.x) CTP 1.1. SQL Server 2017 (14.x) CTP 1.1 以降では、data_file は Azure Blob Storage に格納することができます。 例については、「Azure BLOB ストレージのデータに一括アクセスする例」をご覧ください。

重要

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

BULK エラー処理オプション

ERRORFILE

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

エラー ファイルはコマンドの実行開始時に作成されます。 存在するファイルの場合にはエラーが発生し、 さらに、拡張子 .ERROR.txt の制御ファイルが作成されます。 このファイルにはエラー ファイルの各行の参照と、エラーの診断が含まれています。 エラーが修正されると、データは読み込み可能になります。 適用対象: SQL Server 2017 (14.x) CTP 1.1. SQL Server 2017 (14.x) 以降では、error_file_path は Azure Blob Storage に格納することができます。

ERRORFILE_DATA_SOURCE_NAME

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

MAXERRORS

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

既定の maximum_errors は 10 です。

Note

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

BULK データ処理オプション

FIRSTROW

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

LASTROW

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

ROWS_PER_BATCH

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

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] ) データ ファイル内のデータの並べ替え方法を指定する省略可能なヒントです。 既定では、一括操作はデータ ファイルが並べ替えられていないことを前提に実行されます。 指定された順序を利用してクエリ オプティマイザーでより効率的なクエリ プランを生成することができる場合、パフォーマンスが向上する可能性があります。 並べ替えの指定は、次の場合に役立ちます。

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

UNIQUE

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

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

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

SINGLE_BLOB

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

重要

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

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

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

重要

CODEPAGE は、Linux ではサポートされていないオプションです。

注意

フォーマット ファイルの各列に対して照合順序名を指定することをお勧めします (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

FORMAT= 'CSV' 適用対象: SQL Server 2017 (14.x) CTP 1.1. RFC 4180 標準に準拠しているコンマ区切り値ファイルを指定します。

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

FORMATFILE

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

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

フォーマット ファイルについては、「データの一括インポートでのフォーマット ファイルの使用 (SQL Server)」をご覧ください。

適用対象: SQL Server 2017 (14.x) CTP 1.1. SQL Server 2017 (14.x) CTP 1.1 以降では、format_file_path は Azure Blob Storage に格納することができます。 例については、「Azure BLOB ストレージのデータに一括アクセスする例」をご覧ください。

FIELDQUOTE

FIELDQUOTE= 'field_quote' 適用対象: SQL Server 2017 (14.x) CTP 1.1. CSV ファイルで引用符文字として使用される文字を指定します。 指定されていない場合は、RFC 4180 標準の定義に従って引用符文字 (") が引用符文字として使用されます。

解説

OPENROWSET は、OLE DB データ ソースからリモート データにアクセスするときに使用できます。ただしこの場合、指定したプロバイダーに対して DisallowAdhocAccess レジストリ オプションが明示的に 0 に設定されており、Ad Hoc Distributed Queries 詳細構成オプションが有効になっている必要があります。 これらのオプションが設定されていない場合は、既定でアドホック アクセスは許可されません。

リモートの OLE DB データ ソースにアクセスするとき、信頼関係接続のログイン ID は、クライアントの接続先サーバーからクエリの対象サーバーに自動的に委任されるわけではありません。 したがって、認証の委任を構成する必要があります。

指定したデータ ソースにおいて、OLE DB プロバイダーが複数のカタログとスキーマをサポートする場合は、カタログ名とスキーマ名を指定する必要があります。 カタログとスキーマの値は、OLE DB プロバイダーではサポートしていない場合は省略できます。 プロバイダーがスキーマ名しかサポートしていない場合は、スキーマ.オブジェクト という形式の 2 部構成の名前を指定する必要があります。 プロバイダーがカタログ名しかサポートしていない場合は、カタログ.スキーマ.オブジェクト という形式の 3 部構成の名前を指定する必要があります。 SQL Server Native Client OLE DB プロバイダーを使用するパススルー クエリには、3 つの部分で構成される名前を指定する必要があります。 詳細については、「Transact-SQL 構文表記規則」を参照してください。

OPENROWSET の引数に変数は指定できません。

FROM 句での OPENDATASOURCE、OPENQUERY、または OPENROWSET の呼び出しは、更新のターゲットとして使用されるこれらの関数の呼び出しとは別に評価されます。これは、両方の呼び出しに同じ引数が指定されている場合にも当てはまります。 特に、いずれか一方の呼び出しの結果に適用されるフィルター条件または結合条件は、もう一方の結果に影響しません。

OPENROWSET を BULK オプションと共に使用する

次の 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)」をご覧ください。 一括インポートによって実行される行挿入操作がトランザクション ログに記録される条件について詳しくは、「 一括インポートで最小ログ記録を行うための前提条件」をご覧ください。

注意

OPENROWSET を使用するにあたっては、SQL Server で権限借用がどのように処理されるかを理解しておくことが重要です。 セキュリティの考慮事項については、BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート (SQL Server) に関するページをご覧ください。

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

OPENROWSET(BULK...) では、指定がない場合、SQLCHAR、SQLNCHAR、および SQLBINARY データの最大の長さが 8000 バイトを超えないものと想定されます。 インポートされるデータが 8000 バイトを超える varchar(max) 、nvarchar(max) 、または varbinary(max) オブジェクトを含む 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 のアクセス許可が必要です。

例

A. OPENROWSET を SELECT および SQL Server Native Client OLE DB プロバイダーと共に使用する

次の例では、SQL Server Native Client OLE DB プロバイダーを使用して、リモート サーバー HumanResources.Department のデータベース AdventureWorks2019 のテーブル 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 AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. Microsoft OLE DB Provider for Jet を使用する

次の例では、Microsoft OLE DB Provider for Jet を介して、Microsoft Access Northwind データベース内のテーブル Customers にアクセスします。

注意

この例では、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. OPENROWSET と INNER JOIN 内の別のテーブルを使用する

次の例では、ローカル インスタンスの 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) 列に一括挿入する

次の例では、小さなテーブルを作成し、ルート ディレクトリ 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 プロバイダーをフォーマット ファイルと共に使用して、テキスト ファイルから行を取得する

次の例では、フォーマット ファイルを使用して、タブ区切りのテキスト ファイル 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. フォーマット ファイルとコード ページを指定する

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

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) CTP 1.1.

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 ファイルのデータにアクセスする

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 で OBDC データ ソース アプリケーションの [ドライバー] タブを開きます。 64 ビット バージョンの sqlservr.exe では機能しない、32 ビットの Microsoft Text Driver (*.txt, *.csv) があります。
• Azure SQL Database でサポートされるのは、Azure Blob Storage からの読み取りのみです。

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

適用対象: SQL Server 2017 (14.x) CTP 1.1. 次の例では、Shared Access Signature に対して作成された Azure ストレージ アカウントとデータベース スコープ資格情報のコンテナーを指している外部データ ソースを使用します。

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

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

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***************';

 -- 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/curriculum'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

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 データベース でサポートされるのは、Azure Blob Storage からの読み取りのみです。

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

適用対象: SQL Server 2022 (16.x)

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

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]

その他の例

INSERT...SELECT * FROM OPENROWSET(BULK...) のその他の使用例については、次のトピックをご覧ください。

• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
• データの一括インポート時の ID 値の保持 (SQL Server)
• 一括インポート中の NULL の保持または既定値の使用 (SQL Server)
• データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
• 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)

参照

• DELETE (Transact-SQL)
• FROM (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)