OPENROWSET BULK （Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 分析终结点Microsoft Fabric 中的仓库Microsoft Fabric 中的 SQL 数据库

该 OPENROWSET 函数从一个或多个文件读取数据，并将内容作为行集返回。 根据服务的不同，文件可能存储在 Azure Blob 存储、Azure Data Lake 存储、本地磁盘、网络共享等。 你可以读取各种文件格式，比如文本/CSV、Parquet或JSON行。

你可以在查询的从句中引用函数OPENROWSETFROM，就像引用表名一样。 用它读取语句中SELECT的数据，或更新 、 INSERT、 CTASDELETEMERGE语CETAS句中的UPDATE目标数据。

• 用于 OPENROWSET(BULK) 读取外部数据文件的数据。
• 使用 OPENROWSET 无须 BULK 读取其他数据库引擎。 有关详细信息，请参阅 OPENROWSET（Transact-SQL）。

Tip

本文及其OPENROWSET(BULK)语法在不同SQL 数据库引擎平台上有所不同。

对于Microsoft Fabric Data Warehouse语法，请在版本下拉列表中选择Fabric Data Warehouse。

其他平台上类似示例的详细信息和链接：

• 有关 Azure SQL 数据库中的详细信息 OPENROWSET ，请参阅 使用 Azure SQL 数据库进行数据虚拟化。
• 有关 Azure SQL 托管实例中的详细信息 OPENROWSET ，请参阅 使用 Azure SQL 托管实例进行数据虚拟化。
• 有关 Azure Synapse 中无服务器 SQL 池的信息和示例，请参阅 如何在 Azure Synapse Analytics 中使用无服务器 SQL 池使用 OPENROWSET。
• Azure Synapse 中的专用 SQL 池不支持该 OPENROWSET 函数。

Transact-SQL 语法约定

Syntax

对于SQL Server、Azure SQL数据库、Fabric中的SQL数据库和Azure SQL托管实例：

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

某些选项特定于格式，而另一些 OPENROWSET 选项则为通用选项。 例如，行分隔符和字段分隔符仅对分隔文本（CSV/TSV）有意义，而DATA_SOURCE和 MAXERRORS 等选项适用于所有格式。 下表总结了最常见格式支持的选项。

选项	CSV（1.0）	CSV（2.0）	拼花地板	JSONL
DATA_SOURCE、ROWS_PER_BATCH、MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE、ERRORFILE_DATA_SOURCE、FORMATFILE、FORMATFILE_DATA_SOURCE	Supported	Supported	不支持	Supported
CODEPAGE、DATAFILETYPE	Supported	Supported	不支持	Supported
第一排	Supported	Supported	不支持	Supported
ROWTERMINATOR、FIELDTERMINATOR、FIELDQUOTE、ESCAPECHAR	Supported	Supported	不支持	不支持
解析器_版本	Supported	Supported	不支持	不支持
最后一条	Supported	不支持	不支持	不支持
HEADER_ROW	不支持	Supported	不支持	不支持
SINGLE_BLOB、SINGLE_CLOB、SINGLE_NCLOB	不支持	不支持	不支持	不支持

Arguments

该选项的参数 BULK 允许对开始和结束读取数据的位置、如何处理错误以及解释数据的方式进行重大控制。 例如，可以指定数据文件读取为 varbinary、 varchar 或 nvarchar 类型的单行单列行集。 默认行为详见随后的参数说明。

有关如何使用 BULK 选项的信息，请参阅本文后面的“ 备注 ”部分。 有关该 BULK 选项所需权限的信息，请参见本文后面的 权限 部分。

有关准备数据进行批量导入的信息，请参阅 准备数据进行批量导出或导入。

BULK

读取并返回为行集的数据文件 OPENROWSET 的路径或URI。

URI 可以引用 Azure Data Lake 存储或 Azure Blob 存储。 要读取和返回其数据作为行集的数据文件的 URI。

支持的路径格式为：

• <drive letter>:\<file path> 访问本地磁盘上的文件
• \\<network-share\<file path> 访问网络共享上的文件
• adls://<container>@<storage>.dfs.core.windows.net/<file path> 访问 Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> 访问 Azure Blob 存储
• s3://<ip-address>:<port>/<file path> 访问与 s3 兼容的存储

Note

本文和支持的 URI 模式在不同平台上有所不同。 对于Microsoft Fabric Data Warehouse中可用的URI模式，请在版本下拉列表中选择Fabric Data Warehouse。

从 SQL Server 2017 （14.x）开始， data_file 可以位于 Azure Blob 存储中。 有关示例，请参阅Azure Blob 存储中批量访问数据的示例。

URI可以包含 * 匹配任意字符序列的字符，因此 OPENROWSET 可以对URI进行模式匹配。 此外，URI 可以以 /** 实现递归遍历所有子文件夹。 在 SQL Server 中，此行为从 SQL Server 2022（16.x）开始可用。

例如：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

下表显示了URI可以引用的存储类型：

版本	本地	Azure 存储	Fabric 中的 OneLake	S3	Google Cloud （GCS）
SQL Server 2017 （14.x）、SQL Server 2019 （15.x）	是的	是的	否	否	否
SQL Server 2022 （16.x）	是的	是的	否	是的	否
Azure SQL 数据库	否	是的	否	否	否
Azure SQL 托管实例	否	是的	否	否	否
Azure Synapse Analytics 中的无服务器 SQL 池	否	是的	是的	否	否
在 Microsoft Fabric 中的 Microsoft Fabric 和 SQL 分析终结点中Microsoft Fabric 仓库	否	是的	是的	是的，使用 OneLake in Fabric快捷键	是的，使用 OneLake in Fabric快捷键
Microsoft Fabric 中的 SQL 数据库	否	是的，使用 OneLake in Fabric快捷键	是的	是的，使用 OneLake in Fabric快捷键	是的，使用 OneLake in Fabric快捷键

DATA_SOURCE

DATA_SOURCE 定义数据文件路径的根位置。 它允许你在路径中使用 BULK 相对路径。 创建数据源为 CREATE EXTERNAL DATA SOURCE。

除了根地址外，它还可以定义一个自定义凭证来访问该位置的文件。

例如：

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

文件格式选项

CODEPAGE

指定该数据文件中数据的代码页。 CODEPAGE 仅当数据包含字符值超过 127 或小于 32 的 字符、 varchar 或 文本 列时，才相关。 有效的值为 ACP、 OEM、 RAW或特定的代码页：

CODEPAGE 值	Description
ACP	将 char、 varchar 或 文本 数据类型的列从 ANSI/Microsoft Windows 代码页（ISO 1252）转换为 SQL Server 代码页。
OEM（默认值）	将 char、 varchar 或 文本 数据类型的列从系统 OEM 代码页转换为 SQL Server 代码页。
RAW	不执行从一个代码页到另一个代码页的转换。 这是执行最快的选项。
Integer	指示数据文件中字符数据已编码的源代码页，例如 850。

Important

SQL Server 2016（13.x）之前的版本不支持代码页 65001（UTF-8 编码）。 CODEPAGE Linux 上不支持的选项。

Note

我们建议为格式文件中的每个列指定一个排序规则名称，除非你希望 65001 选项优先于排序规则/代码页规范。

DATAFILETYPE

指定 OPENROWSET(BULK) 应读取单字节（ASCII，UTF8）或多字节（UTF16）文件内容。 有效值为 char 和 widechar：

DATAFILETYPE 值	表示的所有数据：
char （默认值）	字符格式。 有关详细信息，请参阅 使用字符格式导入或导出数据。
widechar	Unicode 字符。 有关详细信息，请参阅 使用 Unicode 字符格式导入或导出数据。

FORMAT

指定所引用文件的格式，例如：

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

有效值为“CSV”（ 符合 RFC 4180 标准版的逗号分隔值文件）、“PARQUET”、“DELTA”（版本 1.0）和“JSONL”，具体取决于版本：

版本	CSV	拼花地板	德尔塔	JSONL
SQL Server 2017 （14.x）、SQL Server 2019 （15.x）	是的	否	否	否
SQL Server 2022 (16.x) 及更高版本	是的	是的	是的	否
Azure SQL 数据库	是的	是的	是的	否
Azure SQL 托管实例	是的	是的	是的	否
Azure Synapse Analytics 中的无服务器 SQL 池	是的	是的	是的	否
在 Microsoft Fabric 中的 Microsoft Fabric 和 SQL 分析终结点中Microsoft Fabric 仓库	是的	是的	否	是的
Microsoft Fabric 中的 SQL 数据库	是的	是的	否	否

FORMATFILE

指定格式化文件的完整路径。 SQL Server 支持两种格式化文件类型：XML 和非 XML。

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

你需要一个格式文件来定义结果集中的列类型。 唯一的例外是当你指定 SINGLE_CLOB、 SINGLE_BLOB、 或 SINGLE_NCLOB时;在这种情况下，你不需要格式文件。

有关格式文件的更多信息，请参见使用格式文件批量导入数据（SQL Server）。

从 SQL Server 2017（14.x）开始，可以format_file_path安装在 Azure Blob 存储 中。 有关示例，请参阅Azure Blob 存储中批量访问数据的示例。

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE 定义格式化文件路径的根位置。 通过使用这个数据源，你可以在选项中使用 FORMATFILE 相对路径。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

创建格式 CREATE EXTERNAL DATA SOURCE文件数据源 。 除了根地址外，它还可以定义一个自定义凭证来访问该位置的文件。

文本/CSV 选项

ROWTERMINATOR

指定要用于 char 和 widechar 数据文件的行终止符，例如：

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

默认行终止符为 \r\n（换行符）。 有关详细信息，请参阅 “指定字段和行终止符”。

FIELDTERMINATOR

指定要用于 char 和 widechar 数据文件的字段终止符，例如：

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

默认字段终止符为 , （逗号）。 有关详细信息，请参阅 指定字段和行终止符。 例如，若要从文件读取制表符分隔的数据：

现场引用

自 SQL Server 2017（14.x）起，此参数指定在 CSV 文件中用作引号字符的字符，如以下纽约示例所示：

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

只能将单个字符指定为此选项的值。 如果未指定，则引号字符 （"） 用作 RFC 4180 标准中定义的引号字符。 字符 FIELDTERMINATOR （例如，逗号）可以放在字段引号中，它将被视为用 FIELDQUOTE 字符包装的单元格中的常规字符。

例如，若要读取以前的纽约示例 CSV 数据集，请使用 FIELDQUOTE = '"'。 地址字段的值将保留为单个值，而不是按 （quote） 字符中的 " 逗号拆分为多个值。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

错误处理选项

错误文件

指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。 这些行将按原样从数据文件复制到此错误文件中。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

错误文件在开始执行命令时创建。 如果文件已存在，则会引发错误。 此外，还会创建一个带有扩展名 .ERROR.txt 的控制文件。 此文件引用错误文件中的每一行并提供错误诊断。 更正错误后，可以加载数据。

从 SQL Server 2017 (14.x) 开始，error_file_path 可位于 Azure Blob 存储中。

ERRORFILE_DATA_SOURCE

从 SQL Server 2017 （14.x）开始，此参数是一个命名的外部数据源，指向错误文件的位置，该文件将包含导入过程中发现的错误。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

有关详细信息，请参阅CREATE EXTERNAL DATA SOURCE（Transact-SQL）。

MAXERRORS（最大错误）

指定格式化文件中定义的最大语法错误数或不符合的行数，在引发异常之前 OPENROWSET 可能发生。 在到达之前 MAXERRORS ， OPENROWSET 请忽略每个错误的行，不加载该行，并将错误行计数为一个错误。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

maximum_errors的默认值为 10。

Note

MAX_ERRORS 不适用于 CHECK 约束或转换 货币 和 bigint 数据类型。

数据处理选项

第一排

指定要加载的第一行的行号。 默认值为 1。 该值表示指定数据文件的第一行。 通过对行终止符进行计数来确定行号。 FIRSTROW 基于 1。

最后一条

指定要加载的最后一行的行号。 默认值为 0。 该值表示指定数据文件的最后一行。

ROWS_PER_BATCH

指定数据文件中近似的数据行数量。 此值是一个估计值，应是实际行数的一个近似值（以一个数量级表示）。 默认情况下，根据文件特征（文件数、文件大小、返回数据类型的大小）估计 ROWS_PER_BATCH。 指定 ROWS_PER_BATCH = 0 与省 ROWS_PER_BATCH略相同。 例如：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

ORDER （ { column [ ASC |DESC ] } [ ,... n ] [ UNIQUE ]

一个用于指定数据文件中数据的排序方式的可选提示。 默认情况下，大容量操作假定数据文件未排序。 如果查询优化器可以利用顺序生成更高效的查询计划，则性能会提高。 以下列表提供了指定排序可能有益的示例：

• 将行插入到具有聚集索引的表，其中行集数据按聚集索引键进行排序。
• 将行集与另一个表联接，其中排序列和联接列匹配。
• 通过排序列聚合行集数据。
• 将行集用作查询子句中的 FROM 源表，其中排序列和联接列匹配。

UNIQUE

指定数据文件没有重复条目。

如果数据文件中的实际行没有按照你指定的顺序排序，或者你指定 UNIQUE 提示和重复键的存在，就会返回错误。

使用 ORDER时必须使用列别名。 列别名列表必须引用该 BULK 子句访问的派生表。 你在条款中 ORDER 指定的列名指向这个列别名列表。 你不能指定大值类型（varchar（max）、nvarchar（max）、varbinary（max）和xml）和大型对象（LOB）类型（文本、ntext和图片）列。

内容选项

SINGLE_BLOB

以 varbinary（max）类型的单行单列行集的形式返回data_file的内容。

Important

仅通过选项SINGLE_BLOB导入XML数据，而非SINGLE_CLOBSINGLE_NCLOB和，因为仅SINGLE_BLOB支持所有Windows编码转换。

SINGLE_CLOB

读取 data_file 为ASCII，并以 varchar（max）类型单行单列行集的形式返回内容，使用当前数据库的排序。

SINGLE_NCLOB

读取 data_file 为Unicode，并返回内容为类型为 nvarchar（max）类型的单行单列行集，使用当前数据库的整合。

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Permissions

要使用 OPENROWSET 外部数据源，你需要以下权限：

• ADMINISTER DATABASE BULK OPERATIONS 或
• ADMINISTER BULK OPERATIONS

以下 T-SQL 示例向主体授予 ADMINISTER DATABASE BULK OPERATIONS 。

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

如果目标存储账户是私有账户，你还必须在容器或存储账户层面为主体分配存储 Blob 数据读取 器角色（或更高级别）的成员资格。

Remarks

• FROM你用的SELECT子句可以调用OPENROWSET(BULK...)，而不是表名，功能完整SELECT。

• 带有 OPENROWSET 选项的 BULK 在 FROM 子句中需要有一个相关名称，也称为范围变量或别名。 如果你不添加 AS <table_alias>，你会收到错误消息491：“必须为from子句中的批量行集指定关联名称。”

• 你可以指定列别名。 如果你没有指定列别名列表，格式文件必须包含列名。 指定列别名会覆盖格式文件中的列名。 例如：

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

• SELECT...FROM OPENROWSET(BULK...) 语句将直接查询文件中的数据，无需将数据导入表中。

• SELECT...FROM OPENROWSET(BULK...)语句可以通过使用格式文件指定列名和数据类型来列出批量列别名。

• 通过在 or MERGE 语句中作为源表INSERT，OPENROWSET(BULK...)你可以批量将数据从数据文件导入到表中。 有关详细信息，请参阅使用BULK INSERT或 OPENROWSET（BULK...）将数据导入到SQL Server。
• 当你在语INSERT句中使用该OPENROWSET BULK选项时，该BULK子句支持表提示。 除了常规表提示（例如 TABLOCK），BULK 子句还可以接受以下专用表提示：IGNORE_CONSTRAINTS（仅忽略 CHECK 和 FOREIGN KEY 约束）、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。 有关详细信息，请参阅表提示 (Transact-SQL)。
• 有关如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句的信息，请参阅批量导入和导出数据 (SQL Server)。 有关批量导入执行的行插入操作何时被记录在事务日志中的信息，请参见批量 导入中最小日志的前置条件。
• 当你用 OPENROWSET (BULK ...) 完整恢复模型导入数据时，日志并不会优化。

Note

使用 OPENROWSET时，请务必了解 SQL Server 如何处理模拟。 有关安全考虑的信息，请参见“使用BULK INSERT”或“OPENROWSET（BULK...）导入数据到SQL Server”。

批量导入 SQLCHAR、SQLNCHAR 或 SQLBINARY 数据

OPENROWSET(BULK...)假设如果未另行指定，则 、 SQLNCHAR或 SQLBINARY 数据的最大长度SQLCHAR不超过 8,000 字节。 如果你在包含任何超过 8,000 字节的 varchar（max）、nvarchar（max） 或 varbinary（max） 对象的 LOB 数据字段中导入数据，你必须使用定义数据字段最大长度的 XML 格式文件。 要指定最大长度，编辑格式文件并声明属性 MAX_LENGTH 。

Note

自动生成的格式化文件未指定 LOB 字段的长度或最大长度。 不过，您可以手动编辑格式文件并指定长度或最大长度。

批量导出或导入 SQLXML 文档

若要批量导出或导入 SQLXML 数据，请在格式化文件中使用下列数据类型之一。

数据类型	Effect
SQLCHAR 或 SQLVARYCHAR	数据在客户端代码页或排序规则隐含的代码页中发送。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式发送数据。
SQLBINARY 或 SQLVARYBIN	不经任何转换即发送数据。

文件元数据函数

有时，你可能需要知道结果集中对应特定行的文件或文件夹源。

你可以使用函数 filepath 和 filename 返回文件名和结果集中的路径。 或者你可以用它们根据文件名和文件夹路径过滤数据。 在接下来的章节中，你会看到简短的描述和示例。

文件名功能

该函数返回该行的文件名。

返回数据类型为 nvarchar（1024）。 为了最佳性能，总是将文件名函数的结果投射为合适的数据类型。 如果你使用字符数据类型，请确保使用合适的长度。

以下示例读取了2017年最后三个月的纽约市黄出租车数据文件，并返回每个文件的乘车次数。 查询部分 OPENROWSET 指定要读取哪些文件。

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

以下示例展示了如何利用 filename() 子句中 WHERE 筛选文件以供读取。 它访问查询部分的整个文件夹 OPENROWSET ，并在子句中过滤文件 WHERE 。

你的结果和之前的例子一样。

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

文件路径函数

该函数返回完整路径或路径的一部分：

• 当你调用 filepath 无参数的函数时，它会返回行所起源的完整文件路径。
• 当你调用 filepath 带有参数的函数时，它会返回路径中与参数指定位置万用符匹配的部分。 例如，参数值为1返回路径中与第一个万用符匹配的部分。

该函数的filepath返回数据类型为 nvarchar（1024）。 为了获得最佳性能，务必将函数结果 filepath 投射到相应的数据类型。 如果你使用字符数据类型，请确保使用合适的长度。

以下示例为2017年最后三个月的纽约黄出租车数据文件。 它返回每个文件路径的骑行次数。 查询部分 OPENROWSET 指定要读取哪些文件。

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

以下示例展示了如何利用 filepath() 子句中 WHERE 筛选文件以供读取。

你可以在 OPENROWSET 查询部分使用万用符，并在子句中筛选文件 WHERE 。 你的结果将与之前的例子相同。

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

本部分提供一般示例来演示如何使用 OPENROWSET BULK 语法。

A. 使用 OPENROWSET 将 BULK INSERT 数据归档到 varbinary（max） 列

适用于： 仅限 SQL Server。

以下示例创建一个小表用于演示目的，并将位于根目录中的文件Text1.txtC:的文件数据插入 varbinary（max） 列。

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

B. 使用带格式化文件的 OPENROWSET BULK 提供程序从文本文件中检索行

适用于： 仅限 SQL Server。

以下示例使用格式化文件检索用制表符分隔的文本文件 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;

C. 指定格式化文件和代码页

适用于： 仅限 SQL Server。

以下示例演示如何同时使用格式化文件和代码页选项。

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

D. 使用格式化文件从 CSV 文件访问数据

适用于： 仅限 SQL Server 2017 （14.x） 及更高版本。

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. 在没有格式化文件的情况下从 CSV 文件访问数据

适用于： 仅限 SQL Server。

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\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'
);

Important

ODBC 驱动程序应为 64 位。 在 Windows 中打开“连接到 ODBC 数据源”（SQL Server 导入和导出向导）应用程序的“驱动程序”选项卡以验证这一点。 有32位 Microsoft Text Driver (*.txt, *.csv) 版本不能和64位版本 sqlservr.exe配合。

F. 从存储在 Azure Blob 存储 上的文件中访问数据

适用于： 仅限 SQL Server 2017 （14.x） 及更高版本。

在 SQL Server 2017（14.x）及更高版本中，以下示例使用一个外部数据源，该数据源指向 Azure 存储帐户中的容器，以及为共享访问签名创建的数据库范围凭据。

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

有关包括配置凭据和外部数据源在内的完整OPENROWSET示例，请参阅Azure Blob 存储中批量访问数据的示例。

G. 从存储在 Azure Blob 存储 上的文件中导入表

以下示例展示了如何使用OPENROWSET命令从你创建SAS密钥的Azure Blob存储位置的CSV文件加载数据。 你把 Azure Blob 存储位置配置为外部数据源。 该过程需要一个数据库范围的凭证，该凭证使用共享访问签名（SAS），并通过用户数据库中的主密钥进行加密。

-- 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 = '<password>';
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***************';

-- Make sure that you don't have a leading ? in the 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 is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

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;

H. 将托管标识用于外部源

适用于： Azure SQL 托管实例和 Azure SQL 数据库

以下示例通过使用管理身份创建凭证，创建外部源，然后从托管在外部源上的CSV加载数据。

首先，创建凭据并将 Blob 存储指定为外部源：

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

接下来，从托管在 Blob 存储上的 CSV 文件加载数据：

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. 使用 OPENROWSET 使用与 S3 兼容的对象存储访问多个 Parquet 文件

适用于：SQL Server 2022 (16.x) 及更高版本。

以下示例访问了多个 Parquet 文件，这些文件都存储在兼容 S3 的对象存储中：

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. 使用 OPENROWSET 从 Azure Data Lake Gen2 访问多个 Delta 表

适用于：SQL Server 2022 (16.x) 及更高版本。

在此例中，数据表容器命名为 Contoso，位于 Azure Data Lake Gen2 存储帐户中。

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. 使用 OPENROWSET 查询公共匿名数据集

以下示例使用公开提供的 NYC 黄色出租车行程记录打开数据集。

首先创建数据源：

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

在与名称模式匹配的文件夹中使用扩展名查询所有文件 .parquet ：

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

更多示例

有关显示使用 OPENROWSET(BULK...)的示例，请参阅以下文章：

• 大容量导入和导出数据 (SQL Server)
• 批量导入和导出 XML 文档的示例 (SQL Server)
• 批量导入数据时保留标识值 (SQL Server)
• 在批量导入期间保留 Null 或默认值 (SQL Server)
• 使用格式文件批量导入数据 (SQL Server)
• 使用字符格式导入或导出数据 (SQL Server)
• 使用格式化文件跳过表列 (SQL Server)
• 使用格式文件跳过数据字段 (SQL Server)
• 使用格式文件将表列映射到数据文件字段 (SQL Server)
• 在 Azure SQL 托管实例 中使用 OPENROWSET 查询数据源
• 指定字段和行终止符（SQL Server）

相关内容

• DELETE （Transact-SQL）
• FROM 子句以及 JOIN、APPLY、PIVOT (Transact-SQL)
• INSERT （Transact-SQL）
• OPENDATASOURCE （Transact-SQL）
• OPENQUERY （Transact-SQL）
• SELECT （Transact-SQL）
• UPDATE （Transact-SQL）
• WHERE （Transact-SQL）