次の方法で共有


OPENROWSET (Transact-SQL)

更新 : 2006 年 12 月 12 日

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

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

トピック リンク アイコンTransact-SQL 構文表記規則

構文

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 ] 

引数

  • '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 Native Client OLE DB プロバイダで認識されるキーワードの一覧については、「初期化プロパティと承認プロパティ」を参照してください。
  • catalog
    指定したオブジェクトが存在するカタログまたはデータベースの名前を指定します。
  • schema
    指定したオブジェクトのスキーマまたはオブジェクト所有者の名前を指定します。
  • object
    操作するオブジェクトを一意に識別するオブジェクト名を指定します。
  • 'query'
    プロバイダに送られ、プロバイダによって実行される文字列定数を指定します。SQL Server のローカル インスタンスでは、このクエリは処理されず、プロバイダから返されたクエリ結果が処理されます (パススルー クエリ)。パススルー クエリは、テーブル形式のデータをテーブル名ではなくコマンド言語のみで公開するプロバイダで使用すると便利です。クエリ プロバイダで OLE DB Command オブジェクトと必要なインターフェイスがサポートされている限り、パススルー クエリはリモート サーバー上でサポートされます。詳細については、「SQL Native Client (OLE DB) リファレンス」を参照してください。
  • BULK
    ファイルからのデータ読み取りに OPENROWSET の BULK 行セット プロバイダを使用します。SQL Server 2005 では、OPENROWSET を使用すると、データを対象テーブルに読み込むことなくデータ ファイルからの読み取りを行えます。このため、OPENROWSET は簡単な SELECT ステートメントで使用できます。

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

    BULK オプションの使用方法については、後の「解説」を参照してください。BULK 操作に必要な権限については、後の「権限」を参照してください。

    ms190312.note(ja-jp,SQL.90).gifメモ :
    OPENROWSET (BULK ...) を完全復旧モデルでデータのインポートに使用する場合、ログ記録は最適化されません。

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

  • 'data_file'
    データを対象テーブルにコピーするデータ ファイルの完全なパスを指定します。
  • FORMATFILE ='format_file_path'
    フォーマット ファイルの完全なパスを指定します。SQL Server 2005 では XML と非 XML の 2 種類のフォーマット ファイルがサポートされます。

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

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

  • < bulk_options >
    BULK オプションの引数を 1 つ以上指定します。
  • CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
    データ ファイル内のデータのコード ページを指定します。CODEPAGE は、127 より大きいか、32 より小さい文字値の charvarchar、または text 型の列がデータに含まれる場合にのみ適用されます。

    CODEPAGE の値 説明

    ACP

    charvarchar、または text 型の列を、ANSI/Microsoft Windows コード ページ (ISO 1252) から SQL Server コード ページに変換します。

    OEM (既定値)

    charvarchar、または text 型の列を、システムの OEM コード ページから SQL Server コード ページに変換します。

    RAW

    コード ページの変換は行われません。これは最も高速なオプションです。

    code_page

    データ ファイルの文字データをエンコードするソース コード ページを示します (例 : 850)。コード ページは、SQL Server 2005 データベース エンジンが入力データを正しく解釈するために必要です。

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

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

  • FIRSTROW **=**first_row
    読み込み開始行の行番号を指定します。既定値は 1 です。指定したデータ ファイルの最初の行を示します。行番号は行ターミネータの数をカウントして決定されます。
  • LASTROW **=**last_row
    読み込み終了行の行番号を指定します。既定値は 0 です。指定したデータ ファイルの最後の行を示します。
  • MAXERRORS **=**maximum_errors
    フォーマット ファイルで定義されている、構文エラーまたは無効な行の最大数を指定します。この数を超えると、OPENROWSET で例外が発生します。OPENROWSET では、無効な行は MAXERRORS に達するまで読み込まれずに無視され、無効な行はエラーとしてカウントされます。

    maximum_errors の既定値は 10 です。

    ms190312.note(ja-jp,SQL.90).gifメモ :
    MAX_ERRORS は、CHECK 制約または money および bigint 型の変換には適用されません。
  • ROWS_PER_BATCH **=**rows_per_batch
    データ ファイル内にあるデータ行の概算数を指定します。この値は実際の行数と同じ順序にする必要があります。

    OPENROWSET では、常にデータ ファイルが単一のバッチとしてインポートされますが、rows_per_batch に値 > 0 を指定した場合、クエリ プロセッサでは、クエリ プランのリソース割り当てのヒントとして rows_per_batch の値が使用されます。

    ROWS_PER_BATCH の既定値はありません。ROWS_PER_BATCH = 0 は、ROWS_PER_BATCH を省略した場合と同じになります。

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

    ms190312.note(ja-jp,SQL.90).gif重要 :
    すべての 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 プロバイダでカタログとスキーマがサポートされない場合は、catalogschema の値を省略してもかまいません。プロバイダでスキーマ名だけがサポートされる場合は、schema**.object という形式の 2 部構成の名前を指定する必要があります。プロバイダでカタログ名だけがサポートされる場合は、catalog.schema.**object という形式の 3 部構成の名前を指定する必要があります。SQL Native Client OLE DB プロバイダを使用するパススルー クエリには、3 つの部分で構成される名前を指定する必要があります。詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。

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)
  • A SELECT…FROM OPENROWSET(BULK...) ステートメントでは、データをテーブルにインポートせずに、ファイル内のデータを直接クエリします。また、フォーマット ファイルを使用して列名を指定することにより一括列の別名を列挙したり、データ型を列挙することもできます。
  • INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントは、データ ファイルから SQL Server テーブルへデータを一括インポートします。詳細については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」を参照してください。
  • INSERT ステートメントと共に使用する OPENROWSET BULK オプションでは、BULK 句でテーブル ヒントがサポートされます。BULK 句では、TABLOCK などの通常のテーブル ヒント以外に、特殊なテーブル ヒント IGNORE_CONSTRAINTS (CHECK 制約と FOREIGN KEY 制約だけを無視)、IGNORE_TRIGGERS、KEEPDEFAULTS、KEEPIDENTITY を使用できます。詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントの使用方法については、「一括データのインポートおよびエクスポート」を参照してください。一括インポート中に実行される行挿入操作がトランザクション ログに記録されるタイミングの詳細については、「一括インポートで最小ログ記録を行うための前提条件」を参照してください。

ms190312.note(ja-jp,SQL.90).gifメモ :
OPENROWSET を使用するにあたっては、SQL Server 2005 で権限借用がどのように処理されるかを理解しておくことが重要です。セキィリティ上の考慮事項については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」を参照してください。

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

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

データ型 結果

SQLCHAR または SQLVARYCHAR

データは、クライアント コード ページまたは照合順序で暗黙的に指定されるコード ページで送られます。

SQLNCHAR または SQLNVARCHAR

データは Unicode として送られます。

SQLBINARY または SQLVARYBIN

データは変換なしで送られます。

権限

OPENROWSET の権限は、OLE DB プロバイダに渡されるユーザー名の権限によって決まります。BULK オプションを使用するには ADMINISTER BULK OPERATIONS 権限が必要です。

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

次の例では、SQL Native Client OLE DB プロバイダ (SQLNCLI) を使用して、リモート サーバー Seattle1 のデータベース AdventureWorks のテーブル HumanResources.Department にアクセスします。SELECT ステートメントは、返す行セットの定義に使用します。プロバイダの文字列には、ServerTrusted_Connection キーワードが含まれます。これらのキーワードは、SQL Native Client OLE DB プロバイダによって認識されます。

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.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 にアクセスします。

ms190312.note(ja-jp,SQL.90).gifメモ :
この例では、Access がインストールされていることが前提です。この例を実行するには、Northwind データベースをインストールする必要があります。Northwind データベースのインストール方法については、「Northwind サンプル データベースと pubs サンプル データベースのダウンロード」を参照してください。
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 から、すべてのデータを選択します。

ms190312.note(ja-jp,SQL.90).gifメモ :
この例では、Access がインストールされていることが前提です。この例を実行するには、Northwind データベースをインストールする必要があります。Northwind データベースのインストール方法については、「Northwind サンプル データベースと pubs サンプル データベースのダウンロード」を参照してください。
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 AdventureWorks
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...) のその他の使用例については、次のトピックを参照してください。

参照

関連項目

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
FILEGROUPPROPERTY (Transact-SQL)
OPENQUERY (Transact-SQL)
行セット関数 (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

その他の技術情報

分散クエリ
一括データのインポートおよびエクスポート
ユーザー定義関数 (データベース エンジン)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 12 月 12 日

新しい内容 :
  • IGNORE_CONSTRAINTS テーブル ヒントでは、CHECK 制約と FOREIGN KEY 制約の両方が無視されることを追加。

2006 年 7 月 17 日

新しい内容 :
  • OLE DB データ ソースをより頻繁に参照する場合は、OPENROWSET ではなくリンク サーバーを使用する必要があるという説明を追加。
  • 「解説」に以下の変更を追加。
    • リモートの OLE DB データ ソースからダブル ホップを介してアクセスするときに、認証委任を構成する必要があるという段落を追加。
    • SQL Native Client OLE DB プロバイダを使用するパススルー クエリには、3 つの部分で構成される名前を指定する必要があるという説明を追加。
    • BULK オプションの使用に関して情報を追加。