OPENROWSET (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 ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] 

引數

• 'provider_name'
  這是一個字元字串，代表在登錄中指定之 OLE DB 提供者的易記名稱 (或 PROGID)。provider_name 沒有預設值。

• 'datasource'
  這是一個對應至特定 OLE DB 資料來源的字串常數。datasource 是指要傳遞到提供者 IDBProperties 介面，將提供者初始化所用的 DBPROP_INIT_DATASOURCE 屬性。這個字串通常都包含資料庫檔案的名稱、資料庫伺服器的名稱，或是提供者尋找資料庫所用的名稱。

• '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 屬性傳入來初始化 OLE DB 提供者。provider_string 通常會封裝將提供者初始化所需的所有連接資訊。如需由 SQL Server Native Client OLE DB 提供者辨識的關鍵字清單，請參閱＜初始化和授權屬性＞。

• catalog
  這是指定之物件所在的目錄或資料庫名稱。

• schema
  這是指定之物件的結構描述或物件擁有者名稱。

• object
  這是唯一識別所處理之物件的物件名稱。

• 'query'
  這是傳給提供者，並且由提供者執行的字串常數。SQL Server 的本機執行個體不會處理這項查詢，但會處理提供者傳回的查詢結果，亦即通過查詢。如果提供者不是透過資料表名稱，而只透過命令語言使用其資料表資料，通過查詢將會很實用。只要查詢提供者支援 OLE DB Command 物件及其必要介面，遠端伺服器就支援通過查詢。如需詳細資訊，請參閱＜SQL Server Native Client (OLE DB) 參考＞。

• BULK
  使用 BULK 資料列集提供者，讓 OPENROWSET 讀取檔案資料。在 SQL Server 中，OPENROWSET 可以從資料檔讀取，而不必將資料載入到目標資料表中。此舉可讓您搭配簡單的 SELECT 陳述式來使用 OPENROWSET。

  BULK 選項的引數可讓您全力控制在哪裡開始和結束資料讀取、如何處理錯誤以及如何解譯資料。例如，您可以指定讓資料檔當做 varbinary、varchar 或 nvarchar 類型的單一資料列、單一資料行資料列集加以讀取。預設行為將在接下來的引數描述中加以描述。

  如需有關如何使用 BULK 選項的詳細資訊，請參閱本主題稍後的＜備註＞。如需有關 BULK 選項所需權限的詳細資訊，請參閱本主題稍後的「權限」。

  [!附註]

  當它以完整復原模式匯入資料時，OPENROWSET (BULK ...) 不會最佳化記錄。

  如需有關準備資料進行大量匯入的詳細資訊，請參閱＜準備大量匯出或匯入的資料＞。

• 'data_file'
  這是要將資料複製到目標資料表之資料檔的完整路徑。

• FORMATFILE ='format_file_path'
  指定格式檔的完整路徑。SQL Server 支援下列兩種類型的格式檔：XML 和非 XML。

  您必須使用格式檔，才能定義結果集中的資料行類型。不過，當指定 SINGLE_CLOB、SINGLE_BLOB 或 SINGLE_NCLOB 時，就不需要格式檔，這是唯一的例外狀況。

  如需有關格式檔的資訊，請參閱＜使用格式檔案大量匯入資料＞。

• < bulk_options >
  為 BULK 選項指定一個或多個引數。

• CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' }
  指定資料檔中之資料的字碼頁。只有當資料包含字元值大於 127 或小於 32 的 char、varchar 或 text 資料行時，CODEPAGE 才會相關。

  [!附註]

  我們建議您在格式檔案中，針對每一個資料行各指定一個定序名稱。

  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 不支援字碼頁 65001 (UTF-8 編碼)。

• ERRORFILE ='file_name'
  指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。這些資料列會「依照原狀」，從資料檔複製到這個錯誤檔中。

  錯誤檔是在開始執行命令時建立。如果檔案已經存在，就會引發錯誤。另外，還會建立一個副檔名為 .ERROR.txt 的控制檔。這個檔案會參考錯誤檔中的每個資料列，且會提供錯誤診斷。錯誤更正之後，就能夠載入資料。

• FIRSTROW =first_row
  指定要載入之第一個資料列的號碼。預設值是 1。這表示指定之資料檔中的第一個資料列。資料列號碼是由計算資料列結束字元所決定。FIRSTROW 是以 1 為基底。

• LASTROW =last_row
  指定要載入之最後一個資料列的號碼。預設值是 0。這表示指定的資料檔中的最後一個資料列。

• MAXERRORS =maximum_errors
  指定最多出現幾個語法錯誤或不符合的資料列 (如格式檔所定義) 之後，OPENROWSET 便會擲出例外狀況。只要尚未到達 MAXERRORS，OPENROWSET 會忽略所有不正確的資料列，也不載入它，並將這個不正確的資料列計為一個錯誤。

  maximum_errors 的預設值是 10。

  [!附註]

  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。

• 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_BLOB 選項匯入 XML 資料，而不要使用 SINGLE_CLOB 和 SINGLE_NCLOB，因為只有 SINGLE_BLOB 支援所有的 Windows 編碼轉換。

• SINGLE_CLOB
  以 ASCII 格式讀取 data_file，並且使用目前資料庫的定序，將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。

• SINGLE_NCLOB
  以 UNICODE 格式讀取 data_file，並且使用目前資料庫的定序，將內容當做 nvarchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。

備註

唯有針對指定的提供者將 DisallowAdhocAccess 登錄選項明確設為 0，且已啟用 Ad Hoc Distributed Queries 進階組態選項時，才可使用 OPENROWSET 來存取 OLE DB 資料來源中的遠端資料。若未設定這些選項，預設行為便不允許特定存取。

存取遠端 OLE DB 資料來源時，用戶端連接到將進行查詢之伺服器所在的伺服器上，不會自動委派信任連接的登入識別。此時必須設定驗證委派。如需詳細資訊，請參閱＜設定委派之連結的伺服器＞。

如果 OLE DB 提供者支援指定之資料來源中的多個目錄和結構描述，則需要用到目錄和結構描述名稱。如果 OLE DB 提供者不支援 catalog 和 schema 的值，就可以將它們省略。如果提供者只支援結構描述名稱，就必須指定 schema**.object 格式的兩部分名稱。如果提供者只支援目錄名稱，則必須指定 catalog.schema.**object 格式的三部分名稱。使用 SQL Server Native Client OLE DB 提供者的通過查詢必須指定三部分的名稱。如需詳細資訊，請參閱＜Transact-SQL 語法慣例 (Transact-SQL)＞。

OPENROWSET 不接受其引數的變數。

FROM 子句中 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何呼叫都會與當做更新目標使用之這些函數的任何呼叫進行個別且獨立的評估，即使完全相同的引數套用至這兩種呼叫也一樣。尤其，針對其中一個呼叫結果所套用的篩選或聯結條件對於另一個呼叫的結果沒有作用。

搭配 BULK 選項使用 OPENROWSET

權限

OPENROWSET 權限是由傳遞給 OLE DB 提供者之使用者名稱的權限所決定。若要使用 BULK 選項，需要 ADMINISTER BULK OPERATIONS 權限。

範例

A. 搭配 SELECT 和 SQL Server Native Client OLE DB 提供者來使用 OPENROWSET

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2008R2.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

B. 使用 Microsoft OLE DB Provider for Jet

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers);
GO

C. 在 INNER JOIN 中使用 OPENROWSET 和另一份資料表

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) 資料行中

USE AdventureWorks2008R2;
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 提供者，從文字檔擷取資料列

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

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;

其他範例

請參閱

參考

概念

其他資源