OPENROWSET (Transact-SQL)OPENROWSET (Transact-SQL)
09/30/2019
本文内容
适用于：Applies to: 
SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database
包含访问 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. OPENROWSET 函数可以在查询的 FROM 子句中引用，就好象它是一个表名。The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. 依据 OLE DB 提供程序的功能，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。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.
语法Syntax
OPENROWSET
( { 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { | 'query' }
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
::= [ catalog. ] [ schema. ] object
::=
[ , DATASOURCE = 'data_source_name' ]
[ , ERRORFILE = 'file_name' ]
[ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]
[ , MAXERRORS = maximum_errors ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]
-- bulk_options related to input file format
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , FORMAT = 'CSV' ]
[ , FIELDQUOTE = 'quote_characters']
[ , FORMATFILE = 'format_file_path' ]
[ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]
备注
若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
参数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. 提供程序名称示例是 Microsoft.Jet.OLEDB.4.0、SQLNCLI 或 MSDASQL。Provider name examples are Microsoft.Jet.OLEDB.4.0, SQLNCLI, or MSDASQL.
'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.
数据源可以是 Microsoft.Jet.OLEDB.4.0 提供程序的文件路径 C:\SAMPLES\Northwind.mdb'，也可以是 SQLNCLI 提供程序的连接字符串 Server=Seattle1;Trusted_Connection=yes;。Data source can be file path C:\SAMPLES\Northwind.mdb' for Microsoft.Jet.OLEDB.4.0 provider, or connection string Server=Seattle1;Trusted_Connection=yes; for SQLNCLI provider.
'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.
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\SAMPLES\Northwind.mdb';
'admin';
'password',
Customers) AS a;
'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 ServerSQL Server Native Client OLE DB 提供程序识别的关键字列表，请参阅初始化和授权属性。For a list of keywords that are recognized by the SQL ServerSQL Server Native Client OLE DB provider, see Initialization and Authorization Properties.
SELECT d.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
Department) AS d;
包含 OPENROWSET 应读取的数据的远程表或视图。Remote table or view containing the data that OPENROWSET should read. 它可以是包含以下组件的三部分名称对象：It can be three-part-name object with the following components:
catalog(可选)- 指定对象所在的目录或数据库的名称 。catalog (optional) - the name of the catalog or database in which the specified object resides.
schema(可选)- 架构名称或指定对象的对象所有者名称 。schema (optional) - the name of the schema or object owner for the specified object.
object - 对象名称，唯一地标识出将要操作的对象 。object - the object name that uniquely identifies the object to work with.
SELECT d.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
AdventureWorks2012.HumanResources.Department) AS d;
'query''query'
字符串常量，发送到访问接口并由访问接口执行。Is a string constant sent to and executed by the provider. SQL ServerSQL Server 的本地实例不处理该查询，但处理由访问接口返回的查询结果(传递查询)。The local instance of SQL ServerSQL 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.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT TOP 10 GroupName, Name
FROM AdventureWorks2012.HumanResources.Department') AS a;
BULKBULK
使用 OPENROWSET 的 BULK 行集访问接口读取文件中的数据。Uses the BULK rowset provider for OPENROWSET to read data from a file. 在 SQL ServerSQL Server 中，OPENROWSET 无需将数据文件中的数据加载到目标表，便可读取这些数据。In SQL ServerSQL 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 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
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. 例如，可以指定以类型为 varbinary、varchar 或 nvarchar 的单行单列行集的形式读取数据文件 。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.
BULK 'data_file'BULK 'data_file'
数据文件的完整路径，该文件的数据将被复制到目标表中。Is the full path of the data file whose data is to be copied into the target table.
SELECT * FROM OPENROWSET(
BULK 'C:\DATA\inv-2017-01-19.csv',
SINGLE_CLOB) AS DATA;
适用于： 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 SQL 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
BULK 错误处理选项BULK Error handling options
ERRORFILEERRORFILE
ERRORFILE ='file_name' 指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件 。ERRORFILE ='file_name' 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_NAMEERRORFILE_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.
MAXERRORSMAXERRORS
MAXERRORS =maximum_errors 指定在 OPENROWSET 引发异常之前允许发生的最多语法错误数或格式有误的行数(如格式化文件中所述) 。MAXERRORS =maximum_errors 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 约束，也不适用于“money”和“bigint”数据类型的转换 。MAX_ERRORS does not apply to CHECK constraints, or to converting money and bigint data types.
BULK 数据处理选项BULK Data processing options
FIRSTROWFIRSTROW
FIRSTROW = first_row 指定要加载的第一行的行号 。FIRSTROW =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.
LASTROWLASTROW
LASTROW = last_row 指定要加载的最后一行的行号 。LASTROW =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.
ROWS_PER_BATCHROWS_PER_BATCH
ROWS_PER_BATCH =rows_per_batch 指定数据文件中近似的数据行数量 。ROWS_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.
ORDERORDER
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.
UNIQUEUNIQUE
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) 类型(text、ntext 和 image)列 。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 选项(而不是 SINGLE_CLOB 和 SINGLE_NCLOB)导入 XML 数据，因为只有 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，使用当前数据库的排序规则将内容作为类型为 nvarchar(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.
SELECT *
FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_NCLOB) AS Document;
BULK 输入文件格式选项BULK Input file format options
CODEPAGECODEPAGE
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、varchar 或 text 列时，CODEPAGE 才适用 。CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values more than 127 or less than 32.
重要
CODEPAGE 不是 Linux 上的支持项。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
说明Description
ACPACP
将数据类型为 char、varchar 或 text 的列由 ANSI/MicrosoftMicrosoft Windows 代码页 (ISO 1252) 转换为 SQL ServerSQL Server 代码页 。Converts columns of char, varchar, or text data type from the ANSI/MicrosoftMicrosoft Windows code page (ISO 1252) to the SQL ServerSQL Server code page.
OEM(默认值)OEM (default)
将数据类型为 char、varchar 或 text 的列由系统 OEM 代码页 (ISO 1252) 转换为 SQL ServerSQL Server 代码页 。Converts columns of char, varchar, or text data type from the system OEM code page to the SQL ServerSQL 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).
FORMATFORMAT
FORMAT = 'CSV' 适用范围： SQL Server 2017 (14.x)SQL Server 2017 (14.x)CTP 1.1。FORMAT = 'CSV' 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.
SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
FORMATFILE = N'D:\XChange\test-csv.fmt',
FIRSTROW=2,
FORMAT='CSV') AS cars;
FORMATFILEFORMATFILE
FORMATFILE ='format_file_path' 指定格式化文件的完整路径 。FORMATFILE ='format_file_path' Specifies the full path of a format file. SQL ServerSQL 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 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.
FIELDQUOTEFIELDQUOTE
FIELDQUOTE= 'field_quote' 适用范围 ：SQL Server 2017 (14.x)SQL Server 2017 (14.x)CTP 1.1。FIELDQUOTE = 'field_quote' 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.
备注Remarks
仅当 DisallowAdhocAccess 注册表选项针对指定的提供程序显式设置为 0，并且启用 Ad Hoc Distributed Queries 高级配置选项时，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 提供程序并不支持多个目录和架构，则可以忽略 catalog 和 schema 的值 。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 ServerSQL Server Native Client OLE DB 提供程序的传递查询指定由三部分组成的名称。Three-part names must be specified for pass-through queries that use the SQL ServerSQL Server Native Client OLE DB provider.
OPENROWSET 不接受其参数的变量。OPENROWSET does not accept variables for its arguments.
FROM 子句中对 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何调用与对用作更新目标的这些函数的任何调用都是分开独立计算的，即使为两个调用提供的参数相同也是如此。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 选项的 OPENROWSET 在 FROM 子句中需要有一个相关名称，也称为范围变量或别名。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 失败，将导致错误：消息 491，级别 16，状态 1，第 20 行 必须在 FROM 子句中为大容量行集指定相关名称。Failure to add the AS will result in the error: Msg 491, Level 16, State 1, Line 20 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...) 用作 INSERT 或 MERGE 语句中的源表，将数据文件中的数据大容量导入 SQL ServerSQL Server 表中。Using OPENROWSET(BULK...) as a source table in an INSERT or MERGE statement bulk imports data from a data file into a SQL ServerSQL Server table.
OPENROWSET BULK 选项与 INSERT 语句一起使用时，BULK 子句支持表提示。When the OPENROWSET BULK option is used with an INSERT statement, the BULK clause supports table hints. 除了常规表提示(例如 TABLOCK)，BULK 子句还可以接受以下专用表提示：IGNORE_CONSTRAINTS(仅忽略 CHECK 和 FOREIGN KEY 约束)、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。In 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. 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). 有关何时在事务日志中记录由批量导入执行的行插入操作的信息，请参阅 《Prerequisites for Minimal Logging in Bulk Import》(批量导入的最小日志记录的先决条件)。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 ServerSQL Server 是如何处理模拟的。When you use OPENROWSET, it is important to understand how SQL ServerSQL Server handles impersonation.
大容量导入 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 或 ADMINISTER DATABASE BULK OPERATIONS 权限。To use the BULK option requires ADMINISTER BULK OPERATIONS or ADMINISTER DATABASE BULK OPERATIONS permission.
示例Examples
A.A. 将 OPENROWSET 与 SELECT 和 SQL Server Native Client OLE DB 访问接口一起使用Using OPENROWSET with SELECT and the SQL Server Native Client OLE DB Provider
以下示例使用 SQL ServerSQL Server Native Client OLE DB 提供程序访问 HumanResources.Department 表，该表位于远程服务器 Seattle1 上的 AdventureWorks2012AdventureWorks2012 数据库中。The following example uses the SQL ServerSQL Server Native Client OLE DB provider to access the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 database on the remote server Seattle1. (使用 SQLNCLI 并且 SQL ServerSQL Server 将重定向到 SQL ServerSQL Server Native Client OLE DB 访问接口的最新版本。)使用 SELECT 语句定义返回的行集。(Use SQLNCLI and SQL ServerSQL Server will redirect to the latest version of SQL ServerSQL Server Native Client OLE DB Provider.) A SELECT statement is used to define the row set returned. 访问接口字符串包含 Server 和 Trusted_Connection 关键字。The provider string contains the Server and Trusted_Connection keywords. 这些关键字由 SQL ServerSQL Server Native Client OLE DB 提供程序识别。These keywords are recognized by the SQL ServerSQL 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);
重要
Azure SQL 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
C.C. 使用 OPENROWSET 和 INNER JOIN 中的另一个表Using OPENROWSET and another table in an INNER JOIN
以下示例从 SQL ServerSQL Server Northwind 数据库本地实例中的 Customers 表以及存储在同一计算机上的 Access Northwind 数据库中的 Orders 表中选择所有数据。The following example selects all data from the Customers table from the local instance of SQL ServerSQL 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 ;
重要
Azure SQL 数据库Azure SQL Database 仅支持从 Azure blob 存储读取内容。only supports reading from Azure Blob Storage.
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.
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 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
E.E. 将 OPENROWSET BULK 访问接口用于格式化文件以检索文本文件中的行Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file
以下示例使用格式化文件检索用制表符分隔的文本文件 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 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
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 数据库Azure SQL Database 仅支持从 Azure blob 存储读取内容。only supports reading from Azure Blob Storage.
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;
SELECT *
FROM OPENROWSET
( 'MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select * from E:\Tlog\TerritoryData.csv')
;
重要
ODBC 驱动程序应为 64 位。The ODBC driver should be 64-bit. 在 Windows 中打开 OBDC 数据源应用程序的“驱动程序”选项卡来验证这一点 。Open the Drivers tab of the OBDC Data Sources application in Windows to verify this. 32 位的 Microsoft Text Driver (*.txt, *.csv) 无法用于 64 位版本的 sqlservr.exe。There is 32-bit Microsoft Text Driver (*.txt, *.csv) that will not work with a 64-bit version of sqlservr.exe.
Azure SQL 数据库仅支持从 Azure blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage.
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.
J.J. 从 Azure Blob 存储上存储的文件导入表Importing into a table from a file stored on Azure Blob storage
下面的示例演示如何使用 OPENROWSET 命令从已创建 SAS 密钥的 Azure Blob 存储位置中的 csv 文件加载数据。The following example shows how to use the OPENROWSET command to load data from a csv file in an Azure Blob storage location on which you have created a SAS key. Azure Blob 存储位置配置为外部数据源。The Azure Blob storage location is configured as an external data source. 这需要使用共享访问签名的数据库范围的凭据，该签名通过用户数据库中的主密钥进行加密。This requires a database scoped credential using a shared access signature that is encrypted using a master key in the user database.
--> Optional - a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO
--> Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';
-- NOTE: Make sure that you don't have a leading ? in SAS token, and
-- that you have at least read permission on the object that should be loaded srt=o&sp=r, and
-- that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://****************.blob.core.windows.net/curriculum'
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
INSERT INTO achievements with (TABLOCK) (id, description)
SELECT * FROM OPENROWSET(
BULK 'csv/achievements.csv',
DATA_SOURCE = 'MyAzureBlobStorage',
FORMAT ='CSV',
FORMATFILE='csv/achievements-c.xml',
FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;
重要
Azure SQL 数据库仅支持使用 SAS 令牌从 Azure Blob 存储读取内容。Azure SQL Database only supports reading from Azure Blob Storage using SAS token.
其他示例Additional Examples
有关使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 的其他示例，请参阅以下主题：For additional examples that show using INSERT...SELECT * FROM OPENROWSET(BULK...), see the following topics:
另请参阅See Also
扫一扫
4197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
