OPENROWSET (Transact-SQL)OPENROWSET (Transact-SQL)

適用於:是SQL Server (從 2008 開始) 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。Includes all connection information that is required to access remote data from an OLE DB data source. 這個方法是存取連結伺服器資料表的另一個方法,而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. 對於更常用到的 OLE DB 資料來源參考,請改用連結的伺服器。For more frequent references to OLE DB data sources, use linked servers instead. 如需詳細資訊,請參閱 連結的伺服器 (Database Engine)For more information, see Linked Servers (Database Engine). 您可以依照參考資料表名稱的相同方式,在查詢的 FROM 子句中參考 OPENROWSET 函數。The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. 根據 OLE DB 提供者的能力而定,OPENROWSET 函數也可以被當做 INSERTUPDATEDELETE 陳述式的目標資料表加以參考。The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. 雖然查詢可能傳回多個結果集,但是 OPENROWSET 只能傳回第一個。Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET 也支援透過內建 BULK 提供者執行大量作業,可讓檔案資料被讀取,並且當做資料列集傳回。OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


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' } ]   
   [ , DATASOURCE = 'data_source_name' ]
   [ , ERRORFILE = 'file_name' ]  
   [ , ERRORFILE_DATASOURCE = 'data_source_name' ]   
   [ , FIRSTROW = first_row ]   
   [ , LASTROW = last_row ]   
   [ , MAXERRORS = maximum_errors ]   
   [ , ROWS_PER_BATCH = rows_per_batch ]  
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]

   -- bulk_options related to input file format
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]   

引數Arguments

'provider_name''provider_name'
這是一個字元字串,代表在登錄中指定之 OLE DB 提供者的易記名稱 (或 PROGID)。Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name 沒有預設值。provider_name has no default value.

'datasource''datasource'
這是一個對應至特定 OLE DB 資料來源的字串常數。Is a string constant that corresponds to a particular OLE DB data source. datasource 是指要傳遞到提供者的 IDBProperties 介面,將提供者初始化所用的 DBPROP_INIT_DATASOURCE 屬性。datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. 這個字串通常都包含資料庫檔案的名稱、資料庫伺服器的名稱,或是提供者尋找資料庫所用的名稱。Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.

'user_id''user_id'
這是一個字串常數,代表傳遞到指定之 OLE DB 提供者的使用者名稱。Is a string constant that is the user name passed to the specified OLE DB provider. user_id 會指定連線的安全性內容,而且會當做 DBPROP_AUTH_USERID 屬性傳入來初始化提供者。user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id 不可以是 Microsoft Windows 登入名稱。user_id cannot be a Microsoft Windows login name.

'password''password'
這是一個字串常數,代表傳遞到 OLE DB 提供者的使用者密碼。Is a string constant that is the user password to be passed to the OLE DB provider. password 在將提供者初始化時,會當做 DBPROP_AUTH_PASSWORD 屬性來傳入。password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password 不可以是 Microsoft Windows 密碼。password cannot be a Microsoft Windows password.

'provider_string''provider_string'
這是一個提供者特定的連接字串,會當做 DBPROP_INIT_PROVIDERSTRING 屬性傳入來初始化 OLE DB 提供者。Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string 通常會封裝將提供者初始化所需的所有連線資訊。provider_string typically encapsulates all the connection information required to initialize the provider. 如需由 [SQL Server]SQL Server Native Client OLE DB 提供者辨識的關鍵字清單,請參閱初始化和授權屬性For a list of keywords that are recognized by the [SQL Server]SQL Server Native Client OLE DB provider, see Initialization and Authorization Properties.

catalogcatalog
這是指定之物件所在的目錄或資料庫名稱。Is the name of the catalog or database in which the specified object resides.

schemaschema
這是指定之物件的結構描述或物件擁有者名稱。Is the name of the schema or object owner for the specified object.

objectobject
這是唯一識別所處理之物件的物件名稱。Is the object name that uniquely identifies the object to work with.

'query''query'
這是傳給提供者,並且由提供者執行的字串常數。Is a string constant sent to and executed by the provider. [SQL Server]SQL Server 的本機執行個體不會處理這項查詢,但會處理提供者傳回的查詢結果,亦即通過查詢。The local instance of [SQL Server]SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. 如果提供者不是透過資料表名稱,而只透過命令語言使用其資料表資料,通過查詢將會很實用。Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. 只要查詢提供者支援 OLE DB Command 物件與其必要介面,遠端伺服器就支援通過查詢。Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces. 如需詳細資訊,請參閱 SQL Server Native Client (OLE DB) 參考For more information, see SQL Server Native Client (OLE DB) Reference.

BULKBULK
使用 BULK 資料列集提供者,讓 OPENROWSET 讀取檔案資料。Uses the BULK rowset provider for OPENROWSET to read data from a file. [SQL Server]SQL Server 中,OPENROWSET 可以從資料檔讀取,而不必將資料載入到目標資料表中。In [SQL Server]SQL Server, OPENROWSET can read from a data file without loading the data into a target table. 此舉可讓您搭配簡單的 SELECT 陳述式來使用 OPENROWSET。This lets you use OPENROWSET with a simple SELECT statement.

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

BULK 選項的引數可讓您全力控制在哪裡開始和結束資料讀取、如何處理錯誤以及如何解譯資料。The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. 例如,您可以指定讓資料檔當做 varbinaryvarcharnvarchar 類型的單一資料列、單一資料行資料列集加以讀取。For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. 預設行為將在接下來的引數描述中加以描述。The default behavior is described in the argument descriptions that follow.

如需有關如何使用 BULK 選項的詳細資訊,請參閱本主題稍後的<備註>。For information about how to use the BULK option, see "Remarks," later in this topic. 如需有關 BULK 選項所需權限的詳細資訊,請參閱本主題稍後的「權限」。For information about the permissions that are required by the BULK option, see "Permissions," later in this topic.

注意

當它以完整復原模式匯入資料時,OPENROWSET (BULK ...) 不會最佳化記錄。When used to import data with the full recovery model, OPENROWSET (BULK ...) does not optimize logging.

如需準備資料進行大量匯入的資訊,請參閱準備大量匯出或匯入的資料 (SQL Server) 方面的知識。For information on preparing data for bulk import, see Prepare Data for Bulk Export or Import (SQL Server).

'data_file''data_file'
這是要將資料複製到目標資料表之資料檔的完整路徑。Is the full path of the data file whose data is to be copied into the target table.
適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1.
SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1 開始,data_file 就可以保留在 Azure Blob 儲存體中。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1, the data_file can be in Azure blob storage. 例如,請參閱大量存取 Azure Blob 儲存體資料的範例For examples, see Examples of Bulk Access to Data in Azure Blob Storage.

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

<bulk_options><bulk_options>
為 BULK 選項指定一個或多個引數。Specifies one or more arguments for the BULK option.

CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' }CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' }
指定資料檔案中之資料的字碼頁。Specifies the code page of the data in the data file. 只有當資料包含字元值大於 127 或小於 32 的 char****varchartext 資料行時,CODEPAGE 才會相關。CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values more than 127 or less than 32.

重要

在 Linux 上,CODEPAGE 不是支援的選項。CODEPAGE is not a supported option on Linux.

注意

除非您希望 65001 選項的優先順序高於定序/字碼頁指定值,否則建議您在格式檔案中指定每個資料行的定序名稱。We recommend that you specify a collation name for each column in a format file, except when you want the 65001 option to have priority over the collation/code page specification.

CODEPAGE 值CODEPAGE value DescriptionDescription
ACPACP charvarchartext 資料類型的資料行,從 ANSI/ MicrosoftMicrosoft Windows 字碼頁 (ISO 1252) 轉換成 [SQL Server]SQL Server 字碼頁。Converts columns of char, varchar, or text data type from the ANSI/ MicrosoftMicrosoft Windows code page (ISO 1252) to the [SQL Server]SQL Server code page.
OEM (預設值)OEM (default) charvarchartext 資料類型的資料行,從系統 OEM 字碼頁轉換成 [SQL Server]SQL Server 字碼頁。Converts columns of char, varchar, or text data type from the system OEM code page to the [SQL Server]SQL Server code page.
RAWRAW 不進行字碼頁之間的轉換。No conversion occurs from one code page to another. 這是最快的選項。This is the fastest option.
code_pagecode_page 指出在哪一個來源字碼頁,將資料檔中的字元資料加以編碼;例如 850。Indicates the source code page on which the character data in the data file is encoded; for example, 850.

** 重要 ** SQL Server 2016 (13.x)SQL Server 2016 (13.x) 版之前的版本不支援字碼頁 65001 (UTF-8 編碼)。** Important ** Versions prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) do not support code page 65001 (UTF-8 encoding).

ERRORFILE ='file_name'ERRORFILE ='file_name'
指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. 這些資料列會「依照原狀」,從資料檔複製到這個錯誤檔中。These rows are copied into this error file from the data file "as is."

錯誤檔是在開始執行命令時建立。The error file is created at the start of the command execution. 如果檔案已經存在,就會引發錯誤。An error will be raised if the file already exists. 另外,還會建立一個副檔名為 .ERROR.txt 的控制檔。Additionally, a control file that has the extension .ERROR.txt is created. 這個檔案會參考錯誤檔中的每個資料列,且會提供錯誤診斷。This file references each row in the error file and provides error diagnostics. 錯誤更正之後,就能夠載入資料。After the errors have been corrected, the data can be loaded.
適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1. SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始,error_file_path 可以位於 Azure Blob 儲存體中。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), the error_file_path can be in Azure blob storage.

'errorfile_data_source_name''errorfile_data_source_name'
適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1. 這是具名的外部資料來源,指向錯誤檔案的 Azure Blob 儲存體位置,該檔案將包含在匯入期間發現的錯誤。Is a named external data source pointing to the Azure Blob storage location of the error file that will contain errors found during the import. 必須使用 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1 中新增的 TYPE = BLOB_STORAGE 選項來建立外部資料來源。The external data source must be created using the TYPE = BLOB_STORAGE option added in SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1. 如需詳細資訊,請參閱 CREATE EXTERNAL DATA SOURCEFor more information, see CREATE EXTERNAL DATA SOURCE.

FIRSTROW =first_rowFIRSTROW =first_row
指定要載入之第一個資料列的號碼。Specifies the number of the first row to load. 預設值是 1。The default is 1. 這表示指定之資料檔中的第一個資料列。This indicates the first row in the specified data file. 資料列號碼是由計算資料列結束字元所決定。The row numbers are determined by counting the row terminators. FIRSTROW 是以 1 為基底。FIRSTROW is 1-based.

LASTROW =last_rowLASTROW =last_row
指定要載入之最後一個資料列的號碼。Specifies the number of the last row to load. 預設值是 0。The default is 0. 這表示指定的資料檔中的最後一個資料列。This indicates the last row in the specified data file.

MAXERRORS =maximum_errorsMAXERRORS =maximum_errors
指定最多出現幾個語法錯誤或不符合的資料列 (如格式檔所定義) 之後,OPENROWSET 便會擲出例外狀況。Specifies the maximum number of syntax errors or nonconforming rows, as defined in the format file, that can occur before OPENROWSET throws an exception. 只要尚未到達 MAXERRORS,OPENROWSET 會忽略所有不正確的資料列,也不載入它,並將這個不正確的資料列計為一個錯誤。Until MAXERRORS is reached, OPENROWSET ignores each bad row, not loading it, and counts the bad row as one error.

maximum_errors 的預設值為 10。The default for maximum_errors is 10.

注意

MAX_ERRORS 不適用於 CHECK 限制式,也不能轉換 moneybigint 資料類型。MAX_ERRORS does not apply to CHECK constraints, or to converting money and bigint data types.

ROWS_PER_BATCH =rows_per_batchROWS_PER_BATCH =rows_per_batch
指定資料檔中大約有多少資料列。Specifies the approximate number of rows of data in the data file. 這個值應該與實際的資料列數差不多。This value should be of the same order as the actual number of rows.

OPENROWSET 一律將資料檔當做單一批次加以匯入。OPENROWSET always imports a data file as a single batch. 不過,如果您為 rows_per_batch 指定 > 0 的值,查詢處理器會使用 rows_per_batch 的值當作提示,在查詢計劃中配置資源。However, if you specify rows_per_batch with a value > 0, the query processor uses the value of rows_per_batch as a hint for allocating resources in the query plan.

根據預設,ROWS_PER_BATCH 是未知的。By default, ROWS_PER_BATCH is unknown. 指定 ROWS_PER_BATCH = 0 相當於省略 ROWS_PER_BATCH。Specifying ROWS_PER_BATCH = 0 is the same as omitting ROWS_PER_BATCH.

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )
指定要如何排序資料檔中資料的選擇性提示。An optional hint that specifies how the data in the data file is sorted. 依預設,大量作業會假設資料檔沒有排序。By default, the bulk operation assumes the data file is unordered. 如果查詢最佳化工具可以利用指定的順序來產生更有效率的查詢計畫,效能就可能會提升。Performance might improve if the order specified can be exploited by the query optimizer to generate a more efficient query plan. 指定排序可能很有用處的範例包括以下情況:Examples for when specifying a sort can be beneficial include the following:

  • 將資料列插入具有叢集索引的資料表中,其中的資料列集資料會根據叢集索引鍵來排序。Inserting rows into a table that has a clustered index, where the rowset data is sorted on the clustered index key.

  • 將資料列集與另一個資料表聯結,其中的排序資料行和聯結資料行會相符。Joining the rowset with another table, where the sort and join columns match.

  • 依據排序資料行彙總資料列集資料。Aggregating the rowset data by the sort columns.

  • 在查詢的 FROM 子句中使用資料列集當做來源資料表,其中的排序資料行和聯結資料行會相符。Using the rowset as a source table in the FROM clause of a query, where the sort and join columns match.

    UNIQUE 會指定沒有重複項目的資料檔。UNIQUE specifies that the data file does not have duplicate entries.

    如果資料檔中的實際資料列並未根據指定的順序來排序,或是指定了 UNIQUE 提示而且有重複的索引鍵存在,則會傳回錯誤。If the actual rows in the data file are not sorted according to the order that is specified, or if the UNIQUE hint is specified and duplicates keys are present, an error is returned.

    當使用 ORDER 時,需要資料行別名。Column aliases are required when ORDER is used. 資料行別名清單必須參考由 BULK 子句所存取的衍生資料表。The column alias list must reference the derived table that is being accessed by the BULK clause. ORDER 子句中所指定的資料行名稱會參考這個資料行別名清單。The column names that are specified in the ORDER clause refer to this column alias list. 無法指定大數值類型 (varchar(max)nvarchar(max)varbinary(max)xml) 和大型物件 (LOB) 類型 (textntextimage) 資料行。Large value types (varchar(max), nvarchar(max), varbinary(max), and xml) and large object (LOB) types (text, ntext, and image) columns cannot be specified.

    SINGLE_BLOBSINGLE_BLOB
    data_file 的內容當作 varbinary(max) 類型的單一資料列、單一資料行資料列集加以傳回。Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).

重要

建議您只使用 SINGLE_BLOB 選項匯入 XML 資料,而不要使用 SINGLE_CLOB 和 SINGLE_NCLOB,因為只有 SINGLE_BLOB 支援所有的 Windows 編碼轉換。We recommend that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions.

SINGLE_CLOBSINGLE_CLOB
以 ASCII 格式讀取 data_file,並且使用目前資料庫的定序,將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。By reading data_file as ASCII, returns the contents as a single-row, single-column rowset of type varchar(max), using the collation of the current database.

SINGLE_NCLOBSINGLE_NCLOB
以 UNICODE 格式讀取 data_file,並且使用目前資料庫的定序,將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。By reading data_file as UNICODE, returns the contents as a single-row, single-column rowset of type nvarchar(max), using the collation of the current database.

輸入檔案格式選項Input file format options

FORMAT = 'CSV'FORMAT = 'CSV'
適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1.
指定符合 RFC 4180 規範的逗點分隔值檔案。Specifies a comma separated values file compliant to the RFC 4180 standard.

FORMATFILE ='format_file_path'FORMATFILE ='format_file_path'
指定格式檔的完整路徑。Specifies the full path of a format file. [SQL Server]SQL Server 支援兩種類型的格式檔案:XML 和非 XML。supports two types of format files: XML and non-XML.

您必須使用格式檔,才能定義結果集中的資料行類型。A format file is required to define column types in the result set. 不過,當指定 SINGLE_CLOB、SINGLE_BLOB 或 SINGLE_NCLOB 時,就不需要格式檔,這是唯一的例外狀況。The only exception is when SINGLE_CLOB, SINGLE_BLOB, or SINGLE_NCLOB is specified; in which case, the format file is not required.

如需格式檔案的詳細資訊,請參閱使用格式檔案大量匯入資料 (SQL Server)For information about format files, see Use a Format File to Bulk Import Data (SQL Server).

適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1.
SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1 開始,format_file_path 可位於 Azure Blob 儲存體中。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1, the format_file_path can be in Azure blob storage. 例如,請參閱大量存取 Azure Blob 儲存體資料的範例For examples, see Examples of Bulk Access to Data in Azure Blob Storage.

FIELDQUOTE = 'field_quote'FIELDQUOTE = 'field_quote'
適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1.
指定將用來當作 CSV 檔案中引號字元的字元。Specifies a character that will be used as the quote character in the CSV file. 如果未指定,則會使用引號字元 (") 當作引號字元,如 RFC 4180 標準中所定義的。If not specified, the quote character (") will be used as the quote character as defined in the RFC 4180 standard.

RemarksRemarks

唯有針對指定的提供者將 DisallowAdhocAccess 登錄選項明確設定為 0,且已啟用 [隨選分散式查詢] 進階設定選項時,才能使用 OPENROWSET 來存取 OLE DB 資料來源的遠端資料。OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. 若未設定這些選項,預設行為便不允許特定存取。When these options are not set, the default behavior does not allow for ad hoc access.

存取遠端 OLE DB 資料來源時,用戶端連接到將進行查詢之伺服器所在的伺服器上,不會自動委派信任連接的登入識別。When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. 此時必須設定驗證委派。Authentication delegation must be configured.

如果 OLE DB 提供者支援指定之資料來源中的多個目錄和結構描述,則需要目錄和結構描述名稱。Catalog and schema names are required if the OLE DB provider supports multiple catalogs and schemas in the specified data source. 如果 OLE DB 提供者不支援 catalogschema 的值,就可以將它們省略。Values for catalog and )schema can be omitted when the OLE DB provider does not support them. 如果提供者只支援結構描述名稱,就必須指定 schema.object 格式的兩部分名稱。If the provider supports only schema names, a two-part name of the form schema.object must be specified. 如果提供者只支援目錄名稱,則必須指定 catalog.schema.object 格式的三部分名稱。If the provider supports only catalog names, a three-part name of the form catalog.schema.object must be specified. 您必須為使用 [SQL Server]SQL Server Native Client OLE DB 提供者的通過查詢指定三部分的名稱。Three-part names must be specified for pass-through queries that use the [SQL Server]SQL Server Native Client OLE DB provider. 如需詳細資訊,請參閱 Transact-SQL 語法慣例 (Transact-SQL)For more information, see Transact-SQL Syntax Conventions (Transact-SQL).

OPENROWSET 不接受變數作為其引數。OPENROWSET does not accept variables for its arguments.

FROM 子句中 OPENDATASOURCEOPENQUERYOPENROWSET 的任何呼叫都會與當做更新目標使用之這些函數的任何呼叫進行個別且獨立的評估,即使完全相同的引數套用至這兩種呼叫也一樣。Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. 尤其,針對其中一個呼叫結果所套用的篩選或聯結條件對於另一個呼叫的結果沒有作用。In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.

搭配 BULK 選項使用 OPENROWSETUsing OPENROWSET with the BULK Option

下列 Transact-SQLTransact-SQL 增強功能支援 OPENROWSET(BULK...) 函數:The following Transact-SQLTransact-SQL enhancements support the OPENROWSET(BULK...) function:

  • 搭配 SELECT 使用的 FROM 子句可以透過完整的 SELECT 功能呼叫 OPENROWSET(BULK...) 而不是資料表名稱。A FROM clause that is used with SELECT can call OPENROWSET(BULK...) instead of a table name, with full SELECT functionality.

    具有 BULK 選項的 OPENROWSETFROM 子句中需要一個相互關聯名稱,又稱為範圍變數或別名。OPENROWSET with the BULK option requires a correlation name, also known as a range variable or alias, in the FROM clause. 您可以指定資料行別名。Column aliases can be specified. 如果未指定資料行別名清單,格式檔就必須有資料行名稱。If a column alias list is not specified, the format file must have column names. 指定資料行別名會覆寫格式檔中的資料行名稱,例如:Specifying column aliases overrides the column names in the format file, such as:

    FROM OPENROWSET(BULK...) AS table_alias

    FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

    重要

    新增 AS <table_alias> 失敗將會導致錯誤:Failure to add the AS <table_alias> will result in the error:
    訊息 491,層級 16,狀態 1,行 20Msg 491, Level 16, State 1, Line 20
    必須為 FROM 子句中的大量資料列集指定相互關聯名稱。A correlation name must be specified for the bulk rowset in the from clause.

  • SELECT...FROM OPENROWSET(BULK...) 陳述式會直接查詢檔案中的資料,而不將資料匯入資料表中。A SELECT...FROM OPENROWSET(BULK...) statement queries the data in a file directly, without importing the data into a table. SELECT…FROM OPENROWSET(BULK...) 陳述式也可以使用格式檔案來指定資料行名稱和資料類型,以列出大量資料行別名。SELECT…FROM OPENROWSET(BULK...) statements can also list bulk-column aliases by using a format file to specify column names, and also data types.

  • 使用 OPENROWSET(BULK...) 當做 INSERTMERGE 陳述式中的來源資料表會將資料檔中的資料大量匯入 [SQL Server]SQL Server 資料表中。Using OPENROWSET(BULK...) as a source table in an INSERT or MERGE statement bulk imports data from a data file into a [SQL Server]SQL Server table. 如需詳細資訊,請參閱使用 BULK INSERT 或 OPENROWSET(BULK...) 來匯入大量資料 (SQL Server)For more information, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server) .

  • 當搭配 INSERT 陳述式使用 OPENROWSET BULK 選項時,BULK 子句支援資料表提示。When the OPENROWSET BULK option is used with an INSERT statement, the BULK clause supports table hints. 除了一般的資料表提示 (例如 TABLOCK) 之外,BULK 子句也接受下列特殊化資料表提示:IGNORE_CONSTRAINTS (僅忽略 CHECKFOREIGN KEY 限制式)、IGNORE_TRIGGERS``KEEPDEFAULTSKEEPIDENTITYIn addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK and FOREIGN KEY constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. 如需詳細資訊,請參閱資料表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

    如需如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式的資訊,請參閱資料的大量匯入及匯出 (SQL Server)For information about how to use INSERT...SELECT * FROM OPENROWSET(BULK...) statements, see Bulk Import and Export of Data (SQL Server). 如需大量匯入所執行的資料列插入作業於何時記錄到交易記錄的資訊,請參閱大量匯入採用最低限度記錄的必要條件For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import.

注意

使用 OPENROWSET 時,一定要了解 [SQL Server]SQL Server 如何處理模擬。When you use OPENROWSET, it is important to understand how [SQL Server]SQL Server handles impersonation. 如需安全性考量的資訊,請參閱使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大量資料 (SQL Server)For information about security considerations, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

大量匯入 SQLCHAR、SQLNCHAR 或 SQLBINARY 資料Bulk Importing SQLCHAR, SQLNCHAR or SQLBINARY Data

OPENROWSET(BULK...) 會假設,如果未指定,則 SQLCHAR、SQLNCHAR 或 SQLBINARY 資料的最大長度不會超過 8000 個位元組。OPENROWSET(BULK...) assumes that, if not specified, the maximum length of SQLCHAR, SQLNCHAR or SQLBINARY data does not exceed 8000 bytes. 如果要匯入的資料位於 LOB 資料欄位中,該欄位包含了超過 8000 個位元組的任何 varchar(max)nvarchar(max)varbinary(max) 物件,您必須使用 XML 格式檔案來定義資料欄位的最大長度。If the data being imported is in a LOB data field that contains any varchar(max), nvarchar(max), or varbinary(max) objects that exceed 8000 bytes, you must use an XML format file that defines the maximum length for the data field. 若要指定最大長度,請編輯格式檔案,並宣告 MAX_LENGTH 屬性。To specify the maximum length, edit the format file and declare the MAX_LENGTH attribute.

注意

自動產生的格式檔案不會指定 LOB 欄位的長度或最大長度。An automatically generated format file does not specify the length or maximum length for a LOB field. 但是,您可以編輯格式檔案,並手動指定長度或最大長度。However, you can edit a format file and specify the length or maximum length manually.

大量匯出或匯入 SQLXML 文件Bulk Exporting or Importing SQLXML Documents

若要大量匯出或匯入 SQLXML 資料,請在格式檔案中使用下列其中一種資料類型。To bulk export or import SQLXML data, use one of the following data types in your format file.

資料類型Data type 效果Effect
SQLCHAR 或 SQLVARYCHARSQLCHAR or SQLVARYCHAR 資料是使用用戶端字碼頁或定序所隱含的字碼頁所傳送。The data is sent in the client code page or in the code page implied by the collation).
SQLNCHAR 或 SQLNVARCHARSQLNCHAR or SQLNVARCHAR 以 Unicode 格式傳送這份資料。The data is sent as Unicode.
SQLBINARY 或 SQLVARYBINSQLBINARY or SQLVARYBIN 未經任何轉換即傳送這份資料。The data is sent without any conversion.

[權限]Permissions

OPENROWSET 權限是由傳遞給 OLE DB 提供者之使用者名稱的權限所決定。OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. 若要使用 BULK 選項,需要 ADMINISTER BULK OPERATIONS 權限。To use the BULK option requires ADMINISTER BULK OPERATIONS permission.

範例Examples

A.A. 搭配 SELECT 和 SQL Server Native Client OLE DB 提供者來使用 OPENROWSETUsing OPENROWSET with SELECT and the SQL Server Native Client OLE DB Provider

下列範例會使用 [SQL Server]SQL Server Native Client OLE DB 提供者來存取遠端伺服器 Seattle1AdventureWorks2012AdventureWorks2012 資料庫中的 HumanResources.Department 資料表。The following example uses the [SQL Server]SQL Server Native Client OLE DB provider to access the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 database on the remote server Seattle1. (使用 SQLNCLI 和 [SQL Server]SQL Server 將會重新導向至最新版的 [SQL Server]SQL Server Native Client OLE DB 提供者)。SELECT 陳述式是用來定義傳回的資料列集。(Use SQLNCLI and [SQL Server]SQL Server will redirect to the latest version of [SQL Server]SQL Server Native Client OLE DB Provider.) A SELECT statement is used to define the row set returned. 提供者字串包含 ServerTrusted_Connection 關鍵字。The provider string contains the Server and Trusted_Connection keywords. 這些關鍵字是由 [SQL Server]SQL Server Native Client OLE DB 提供者所辨識。These keywords are recognized by the [SQL Server]SQL Server Native Client OLE DB provider.

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.B. 使用 Microsoft OLE DB Provider for JetUsing the Microsoft OLE DB Provider for Jet

下列範例是透過 MicrosoftMicrosoft OLE DB Provider for Jet,存取 MicrosoftMicrosoft Access Customers 資料庫中的 Northwind 資料表。The following example accesses the Customers table in the MicrosoftMicrosoft Access Northwind database through the MicrosoftMicrosoft OLE DB Provider for Jet.

注意

這個範例假設您已經安裝了 Access。This example assumes that Access is installed. 若要執行這個範例,您必須安裝 Northwind 資料庫。To run this example, you must install the Northwind database.

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

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

C.C. 使用 OPENROWSET 和 INNER JOIN 中的另一份資料表Using OPENROWSET and another table in an INNER JOIN

下列範例會從 [SQL Server]SQL Server Northwind 資料庫本機執行個體的 Customers 資料表,以及從儲存在同一部電腦的 Access Northwind 資料庫之 Orders 資料表中,選取所有的資料。The following example selects all data from the Customers table from the local instance of [SQL Server]SQL Server Northwind database and from the Orders table from the Access Northwind database stored on the same computer.

注意

這個範例假設您已經安裝了 Access。This example assumes that Access is installed. 若要執行這個範例,您必須安裝 Northwind 資料庫。To run this example, you must install the Northwind database.

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  

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

D.D. 使用 OPENROWSET 將檔案資料大量插入到 varbinary(max) 資料行中Using OPENROWSET to bulk insert file data into a varbinary(max) column

下列範例會建立一份小型資料表做為示範之用,並且從 Text1.txt 根目錄下的 C: 檔,將檔案資料插入到 varbinary(max) 資料行中。The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

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  

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

E.E. 搭配一個格式檔來使用 OPENROWSET BULK 提供者,從文字檔擷取資料列Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file

下列範例會利用一個格式檔,從 Tab 鍵分隔的文字檔 values.txt 擷取資料列,該檔含有下列資料:The following example uses a format file to retrieve rows from a tab-delimited text file, values.txt that contains the following data:

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

格式檔 values.fmt 會描述 values.txt 中的資料行:The format file, values.fmt, describes the columns in 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  

這是擷取該資料的查詢:This is the query that retrieves that data:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',   
   FORMATFILE = 'c:\test\values.fmt') AS a;  

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

F.F. 指定格式檔案和字碼頁Specifying a format file and code page

下列範例顯示如何同時使用格式檔案和字碼頁選項。The following example show how to use both the format file and code page options at the same time.

INSERT INTO MyTable SELECT a.* FROM  
OPENROWSET (BULK N'D:\data.csv', FORMATFILE =   
    'D:\format_no_collation.txt', CODEPAGE = '65001') AS a;  

G.G. 從具有格式檔案的 CSV 檔案存取資料Accessing data from a CSV file with a format file

適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)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 Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

H.H. 從沒有格式檔案的 CSV 檔案存取資料Accessing data from a CSV file without a format file

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB) AS DATA;

重要

Azure SQL Database 不支援從 Windows 檔案讀取。Azure SQL Database does not support reading from Windows files.

I.I. 從儲存在 Azure Blob 儲存體上的檔案存取資料Accessing data from a file stored on Azure Blob storage

適用於: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 1.1.
下列範例使用指向 Azure 儲存體帳戶中的容器和針對共用存取簽章而建立的資料庫範圍認證的外部資料來源。The following example uses an external data source that points to a container in an Azure storage account and a database scoped credential created for a shared access signature.

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

如需包含設定認證和外部資料來源的完整 OPENROWSET 範例,請參閱大量存取 Azure Blob 儲存體資料的範例For complete OPENROWSET examples including configuring the credential and external data source, see Examples of Bulk Access to Data in Azure Blob Storage.

其他範例Additional Examples

如需示範如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 的其他範例,請參閱下列主題:For additional examples that show using INSERT...SELECT * FROM OPENROWSET(BULK...), see the following topics:

另請參閱See Also

DELETE (Transact-SQL) DELETE (Transact-SQL)
FROM (Transact-SQL) FROM (Transact-SQL)
資料的大量匯入及匯出 (SQL Server) Bulk Import and Export of Data (SQL Server)
INSERT (Transact-SQL) INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL) OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL) OPENQUERY (Transact-SQL)
資料列函數 (Transact-SQL) Rowset Functions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL) sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL) sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)