OPENROWSET (Transact-SQL)
OLE DB データ ソースからリモート データへのアクセスに必要な、すべての接続情報をインクルードします。 これは、リンク サーバー内のテーブルにアクセスする代わりに、OLE DB を使用してリモート データに接続しアクセスする特別な方法です。 OLE DB データ ソースをより頻繁に参照する場合は、代わりにリンク サーバーを使用してください。 詳細については、「リンク サーバー (データベース エンジン)」を参照してください。 OPENROWSET 関数は、クエリの FROM 句の中でテーブル名として参照できます。 OPENROWSET 関数は、INSERT、UPDATE、または DELETE ステートメントの対象テーブルとしても参照できます。ただしこれは OLE DB プロバイダーの制限を受けます。 クエリでは複数の結果セットが返される場合がありますが、OPENROWSET では最初の 1 つだけが返されます。
OPENROWSET では、組み込みの BULK プロバイダーによる一括操作もサポートされ、ファイルのデータを行セットとして読み取り、返すことができます。
.gif "トピック リンク アイコン")
構文
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]
引数
'provider_name'
レジストリの OLE DB プロバイダーの表示名 (または PROGID) を表す文字列を指定します。 provider_name に既定値はありません。'datasource'
特定の OLE DB データ ソースに対応する文字列定数を指定します。 datasource は DBPROP_INIT_DATASOURCE のプロパティで、プロバイダーの IDBProperties インターフェイスに渡され、プロバイダーの初期化に使用されます。 一般的に、この文字列にはデータベース ファイルの名前、データベース サーバーの名前、プロバイダーがデータベースを検索する際に認識する名前のいずれかを指定します。'user_id'
指定した OLE DB プロバイダーに渡されるユーザー名を表す文字列定数を指定します。 user_id は接続のセキュリティ コンテキストを示し、DBPROP_AUTH_USERID プロパティとして渡され、プロバイダーの初期化に使用されます。 user_id に Microsoft Windows のログイン名は指定できません。'password'
OLE DB プロバイダーに渡されるユーザー パスワードを表す文字列定数を指定します。 password は DBPROP_AUTH_PASSWORD プロパティとして引き渡され、プロバイダーの初期化に使用されます。 password に Microsoft Windows のパスワードは指定できません。'provider_string'
プロバイダー固有の接続文字列を指定します。DBPROP_INIT_PROVIDERSTRING プロパティとして渡され、プロバイダーの初期化に使用されます。 provider_string には、通常、プロバイダーを初期化するために必要なすべての接続情報がカプセル化されます。 SQL Server Native Client OLE DB プロバイダーで認識されるキーワードの一覧については、「初期化プロパティと承認プロパティ」を参照してください。catalog
指定したオブジェクトが存在するカタログまたはデータベースの名前を指定します。schema
指定したオブジェクトのスキーマまたはオブジェクト所有者の名前を指定します。object
操作するオブジェクトを一意に識別するオブジェクト名を指定します。'query'
プロバイダーに送られ、プロバイダーによって実行される文字列定数を指定します。 SQL Server のローカル インスタンスでは、このクエリは処理されず、プロバイダーから返されたクエリ結果が処理されます (パススルー クエリ)。 パススルー クエリは、表形式のデータをテーブル名ではなくコマンド言語のみで公開するプロバイダーで使用すると便利です。 クエリ プロバイダーで OLE DB Command オブジェクトと必要なインターフェイスがサポートされている限り、パススルー クエリはリモート サーバー上でサポートされます。 詳細については、「SQL Server Native Client (OLE DB) リファレンス」を参照してください。BULK
ファイルからのデータ読み取りに OPENROWSET の BULK 行セット プロバイダーを使用します。 SQL Server では、OPENROWSET を使用すると、データを対象テーブルに読み込むことなくデータ ファイルからの読み取りができます。 このため、OPENROWSET は簡単な SELECT ステートメントで使用できます。BULK オプションの引数を使用すると、データの読み取りを開始および終了する場所や、エラーの取り扱い、データの解釈方法について、細かく制御することができます。 たとえば、データ ファイルを varbinary、varchar、nvarchar 型の単一行、単一列の行セットとして読み取るように指定できます。 既定の動作については、後の引数の説明を参照してください。
BULK オプションの使用方法の詳細については、後の「解説」を参照してください。 BULK オプションに必要な権限については、後の「権限」を参照してください。
.gif "注意")
注OPENROWSET (BULK ...) を完全復旧モデルでデータのインポートに使用する場合、ログ記録は最適化されません。
一括インポートのデータの準備については、「一括エクスポートまたは一括インポートのデータの準備 (SQL Server)」を参照してください。
'data_file'
データを対象テーブルにコピーするデータ ファイルの完全なパスを指定します。FORMATFILE ='format_file_path'
フォーマット ファイルの完全パスを指定します。 SQL Server では XML と非 XML の 2 種類のフォーマット ファイルがサポートされます。フォーマット ファイルは、結果セットの列の型を定義する場合に必要となります。 ただし SINGLE_CLOB、SINGLE_BLOB、または SINGLE_NCLOB を指定した場合は例外で、この場合はフォーマット ファイルは必要ありません。
フォーマット ファイルの詳細については、「データの一括インポートでのフォーマット ファイルの使用 (SQL Server)」を参照してください。
< bulk_options >
BULK オプションの引数を 1 つ以上指定します。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
コード ページの変換は行われません。 これは最も高速なオプションです。
code_page
データ ファイルの文字データのエンコードに使用されているソースのコード ページを示します (例 : 850)。
.gif "重要な注意事項")
重要SQL Server はコード ページ 65001 (UTF-8 エンコード) をサポートしません。
ERRORFILE ='file_name'
形式エラーがあり、OLE DB 行セットに変換できない行を収集するときに使用するファイルを指定します。 該当する行は、データ ファイルからこのエラー ファイルに "そのまま" コピーされます。エラー ファイルはコマンドの実行開始時に作成されます。 存在するファイルの場合にはエラーが発生し、 拡張子 .ERROR.txt の制御ファイルが作成されます。 このファイルにはエラー ファイルの各行と、エラーの診断が含まれています。 エラーが修正されると、データは読み込み可能になります。
FIRSTROW =first_row
読み込み開始行の行番号を指定します。 既定値は 1 です。 指定したデータ ファイルの最初の行を示します。 行番号は行ターミネータの数をカウントして決定されます。 FIRSTROW は 1 から始まります。LASTROW =last_row
読み込み終了行の行番号を指定します。 既定値は 0 です。 指定したデータ ファイルの最後の行を示します。MAXERRORS =maximum_errors
フォーマット ファイルで定義されている、構文エラーまたは違反行の許容最大数を指定します。この数を超えると、OPENROWSET で例外が発生します。 OPENROWSET は、違反行を読み込まずに無視し、MAXERRORS に達するまで、違反行はエラーとしてカウントされます。maximum_errors の既定値は 10 です。
注MAX_ERRORS は、CHECK 制約または money および bigint 型の変換には適用されません。
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) 型の単一行、単一列の行セットとして返します。重要すべての Windows エンコード変換がサポートされるのは SINGLE_BLOB オプションだけなので、SINGLE_CLOB オプションや SINGLE_NCLOB オプションではなく、SINGLE_BLOB オプションだけを使用して XML データをインポートすることをお勧めします。
SINGLE_CLOB
data_file を ASCII として読み取り、現在のデータベースの照合順序に従い、内容を varchar(max) 型の単一行、単一列の行セットとして返します。SINGLE_NCLOB
data_file を UNICODE として読み取り、現在のデータベースの照合順序に従い、内容を nvarchar(max) 型の単一行、単一列の行セットとして返します。
説明
OPENROWSET は、OLE DB データ ソースからリモート データにアクセスするときに使用できます。ただしこの場合、指定したプロバイダーに対して DisallowAdhocAccess レジストリ オプションが明示的に 0 に設定されており、Ad Hoc Distributed Queries 詳細構成オプションが有効になっている必要があります。 これらのオプションが設定されていない場合は、既定でアドホック接続は許可されません。
リモートの OLE DB データ ソースにアクセスするとき、信頼関係接続のログイン ID は、クライアントの接続先サーバーからクエリの対象サーバーに自動的に委任されるわけではありません。 したがって、認証の委任を構成する必要があります。
指定したデータ ソースにおいて、OLE DB プロバイダーが複数のカタログとスキーマをサポートする場合は、カタログ名とスキーマ名を指定する必要があります。 OLE DB プロバイダーがカタログとスキーマをサポートしない場合は、catalog と schema の値を省略してもかまいません。 プロバイダーがスキーマ名しかサポートしていない場合は、schema**.object という形式の 2 部構成の名前を指定する必要があります。 プロバイダーがカタログ名しかサポートしていない場合は、catalog.schema.**object という形式の 3 部構成の名前を指定する必要があります。 SQL Server Native Client OLE DB プロバイダーを使用するパススルー クエリには、3 つの部分で構成される名前を指定する必要があります。 詳細については、「Transact-SQL 構文表記規則 (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)
SELECT...FROM OPENROWSET(BULK...) ステートメントは、データをテーブルにインポートせずに、ファイル内のデータを直接クエリします。 また、このステートメントでフォーマット ファイルを使用して列名やデータ型を指定すると、一括列の別名を列挙することもできます。
INSERT ステートメントまたは MERGE ステートメント内で OPENROWSET(BULK...) をソース テーブルとして使用すると、データ ファイルから SQL Server テーブルにデータが一括インポートされます。 詳細については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート(SQL Server)」を参照してください。
INSERT ステートメントと共に使用する OPENROWSET BULK オプションでは、BULK 句でテーブル ヒントがサポートされます。 BULK 句では、TABLOCK などの通常のテーブル ヒント以外に、特殊なテーブル ヒント 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 属性を宣言します。
| 注 |
|---|
自動生成されたフォーマット ファイルには、LOB フィールドの最大の長さの指定がありません。 ただし、手作業でフォーマット ファイルを編集して長さまたは最大の長さを指定できます。 |
SQLXML ドキュメントの一括エクスポートまたは一括インポート
SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には次のいずれかを使用します。
データ型 |
結果 |
|---|---|
SQLCHAR または SQLVARYCHAR |
データは、クライアント コード ページまたは照合順序で暗黙的に指定されるコード ページで送られます。 |
SQLNCHAR または SQLNVARCHAR |
データは Unicode として送られます。 |
SQLBINARY または SQLVARYBIN |
データは変換なしで送られます。 |
権限
OPENROWSET 権限は、OLE DB プロバイダーに渡されるユーザー名の権限によって決まります。 BULK オプションを使用するには ADMINISTER BULK OPERATIONS 権限が必要です。
使用例
A. OPENROWSET を SELECT および SQL Server Native Client OLE DB プロバイダーと共に使用する
次の例では、SQL Server Native Client OLE DB プロバイダーを使用して、リモート サーバー Seattle1 のデータベース AdventureWorks2012 のテーブル HumanResources.Department にアクセスします (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);
GO
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 ;
GO
D. OPENROWSET を使用して、ファイル データを varbinary(max) 列に一括挿入する
次の例では、小さなテーブルを作成し、ルート ディレクトリ C: にあるファイル Text1.txt から varbinary(max) 列にファイル データを挿入します。
USE AdventureWorks2012;
GO
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
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;
その他の例
INSERT...SELECT * FROM OPENROWSET(BULK...) のその他の使用例については、次のトピックを参照してください。
関連項目
参照
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)