OPENROWSET (Transact-SQL)

文章目錄

• 使用帶有 BULK 選項的 OPENROWSET
• A. 將 OPENROWSET 與 SELECT 和 SQL Server Native Client OLE DB 提供者一起使用
• B. 使用 Microsoft OLE DB Provider for Jet
• C. 使用 OPENROWSET 和 INNER JOIN 中的另一個表
• D. 使用 OPENROWSET 將檔案資料大容量插入 varbinary(max) 列中
• E. 將 OPENROWSET BULK 提供者用于格式化檔案以檢索文字檔中的行
• 其他樣本

更新日期： 2008 年 10 月 31 日

包 含訪問 OLE DB 資料來源中的遠端資料所需的全部串連資訊。當訪問連結的伺服器中的表時，這種方法是一種替代方法，並且是一種使用 OLE DB 串連並訪問遠端資料的一次性的臨時方法。對於較頻繁引用 OLE DB 資料來源的情況，請改為使用連結的伺服器。有關詳細資料，請參閱 連結的伺服器。 可以在查詢的 FROM 子句中像參考資料表名那樣引用 OPENROWSET 函數。依據 OLE DB 提供者的功能，還可以將 OPENROWSET 函數引用為 INSERT、UPDATE 或 DELETE 語句的目標表。儘管查詢可能返回多個結果集，但 OPENROWSET 只返回第一個結果集。

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 ] 

   [ , 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

使用 OPENROWSET 的 BULK 行集提供者讀取檔案中的資料。在 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 選項（而不是 SINGLE_CLOB 和 SINGLE_NCLOB）匯入 XML 資料，因為只有 SINGLE_BLOB 支援所有的 Windows 編碼轉換。

SINGLE_CLOB

通過以 ASCII 格式讀取 data_file，使用當前資料庫的定序將內容作為類型為 varchar(max) 的單行單列行集返回。

SINGLE_NCLOB

通過以 UNICODE 格式讀取 data_file，使用當前資料庫的定序將內容作為類型為 nvarchar(max) 的單行單列行集返回。

 注釋

只有在以下情況下才能使用 OPENROWSET 訪問 OLE DB 資料來源中的遠端資料：指定提供者的 DisallowAdhocAccess 註冊表選項已顯式設定為 0，並啟用了“即席分散式查詢”進階配置選項。如果未設定這些選項，則預設行為不允許即席訪問。

訪問遠程 OLE DB 資料來源時，伺服器不會自動委託可信串連的登入標識，用戶端通過此登入標識才能串連到正在查詢的伺服器。必須配置身分識別驗證委託。有關詳細資料，請參閱為委託配置連結的伺服器。

如果 OLE DB 提供者在指定的資料來源中支援多個目錄和架構，那麼就需要目錄及架構名稱。如果 OLE DB 提供者並不支援多個目錄和架構，那麼可以忽略 catalog 和 schema 的值。如果提供者只支援架構名稱，那麼必須指定一個格式為 schema.object 的兩部分名稱。如果提供者只支援目錄名稱，那麼必須指定一個格式為 catalog.schema.object 的三部分名稱。必須為使用 SQL Server Native Client OLE DB 提供者的傳遞查詢指定由三部分組成的名稱。有關詳細資料，請參閱 Transact-SQL 文法約定 (Transact-SQL)。

OPENROWSET 不接受參數變數。

使用帶有 BULK 選項的 OPENROWSET

以下 Transact-SQL 增強功能支援 OPENROWSET(BULK...)函數：

• 與 SELECT 一起使用的 FROM 子句可以調用 OPENROWSET(BULK...)而非表名，同時可以實現完整的 SELECT 功能。
  帶有 BULK 選項的 OPENROWSET 在 FROM 子句中需要有一個相關名稱，也稱為範圍變數或別名。可以指定列別名。如果未指定列別名列表，則格式檔案必須具有列名。指定列別名會覆蓋格式檔案中的列名，例如：
  FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
• SELECT...FROM OPENROWSET(BULK...)語句將直接查詢檔案中的資料，無需將資料匯入表中。SELECT…FROM OPENROWSET(BULK...)語句還可以通過使用格式檔案指定列名和資料類型，從而列出大容量列別名。
• 通過將 OPENROWSET(BULK...) 用作 INSERT 或 MERGE 語句中的源表，將資料檔案中的資料大容量匯入 SQL Server 表中。有關詳細資料，請參閱使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大容量資料。
• OPENROWSET BULK 選項與 INSERT 語句一起使用時，BULK 子句支援表提示。BULK 子句除了接受 TABLOCK 等常規表提示之外，還可以接受下列專用表提示：IGNORE_CONSTRAINTS（僅忽略 CHECK 和 FOREIGN KEY 約束）、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。有關詳細資料，請參閱表提示 (Transact-SQL)。

有關如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 語句的資訊，請參閱匯入和匯出大容量資料。有關何時在交易記錄中記錄由大容量匯入執行的行插入操作的資訊，請參閱在大容量匯入中按最小方式記錄日誌的前提條件。

注意：
使用 OPENROWSET 時，請務必瞭解 SQL Server 是如何處理類比的。有關安全注意事項的資訊，請參閱使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大容量資料。

大容量匯入 SQLCHAR、SQLNCHAR 或 SQLBINARY 資料

OPENROWSET(BULK…) 假定（如果未指定） SQLCHAR、SQLNCHAR 或 SQLBINARY 資料的最大長度不會超過 8000 個位元組。如果要匯入的資料所在的 LOB 資料欄位包含超過 8000 位元組的任意 varchar(max)、nvarchar(max) 或 varbinary(max) 對象，則您必須使用為該資料欄位定義最大長度的 XML 格式檔案。若要指定最大長度，請編輯格式檔案並聲明 MAX_LENGTH 屬性。有關詳細資料，請參閱 XML 格式檔案的架構文法。

注意：
自動產生的格式檔案不會為 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 提供者訪問 HumanResources.Department 表，該表位於遠程伺服器 Seattle1 上的 AdventureWorks 資料庫中。（使用 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 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 表。

注意：
該樣本假定已經安裝了 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 表選擇所有資料。

注意：
該樣本假定已經安裝了 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) 列中

以下樣本建立一個用於示範的小型表，並將名為 Text1.txt 的檔案（位於 C: 根目錄）中的檔案資料插入 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...) 示範的其他樣本，請參閱下列主題：

• 大容量匯入和匯出 XML 文檔的樣本
• 大容量匯入資料時保留標識值
• 在大容量匯入期間保留空值或使用預設值
• 使用格式檔案大容量匯入資料
• 使用字元格式設定匯入或匯出資料
• 使用格式檔案跳過表列
• 使用格式檔案跳過資料欄位
• 使用格式檔案將表列映射到資料檔案欄位

 請參閱 參考

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
行集合函式 (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)
其他資源

分散式查詢
匯入和匯出大容量資料
使用者定義函數（資料庫引擎）