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 行。

OPENROWSET可以在查询的子句中FROM引用该函数，就好像它是表名一样。 它可用于读取语句中的数据SELECT，或更新 、UPDATE、INSERT、 DELETEMERGE或CTAS语句中的CETAS目标数据。

• OPENROWSET(BULK) 专为从外部数据文件读取数据而设计。
• OPENROWSET 没有 BULK 设计用于从另一个数据库引擎读取。 有关详细信息，请参阅 OPENROWSET（Transact-SQL）。

本文和平台之间的 OPENROWSET(BULK) 参数集各不相同。

• 对于 Microsoft Fabric 语法， 请在版本下拉列表中选择 Fabric。
• 对于 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例语法， 请在版本下拉列表中选择平台。

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

• 有关 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

Arguments

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

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

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

BULK “data_file_path”

要读取和返回其数据作为行集的数据文件的路径或 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 数据仓库中可用的 URI 模式， 请在版本下拉列表中选择 Fabric。

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

URI 可以包含 * 与任何字符序列匹配的字符，从而允许 OPENROWSET 与 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 Warehouse 和 SQL 分析终结点	否	是的	是的	是的，使用 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”或“code_page”：

CODEPAGE 值	Description
ACP	将 char、 varchar 或 文本 数据类型的列从 ANSI/Microsoft Windows 代码页（ISO 1252）转换为 SQL Server 代码页。
OEM（默认值）	将 char、 varchar 或 文本 数据类型的列从系统 OEM 代码页转换为 SQL Server 代码页。
RAW	不执行从一个代码页到另一个代码页的转换。 这是执行最快的选项。
code_page	指示数据文件中字符数据已编码的源代码页，例如 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 Warehouse 和 SQL 分析终结点	是的	是的	否	是的
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_CLOBSINGLE_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'
);

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

FIELDQUOTE = “field_quote”

自 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 = '"'
);

错误处理选项

ERRORFILE = “file_name”

指定用于收集格式有误且不能转换为 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)。

最大误差 = maximum_errors

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

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

maximum_errors的默认值为 10。

Note

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

数据处理选项

第一排 = first_row

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

LASTROW = last_row

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

ROWS_PER_BATCH = 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）和大型对象（LOB）类型（text、ntext 和 image） 列。

内容选项

SINGLE_BLOB

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

Important

建议仅使用 SINGLE_BLOB 选项（而不是 SINGLE_CLOB 和 SINGLE_NCLOB）导入 XML 数据，因为仅 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> 错误 Msg 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...) 可以使用格式化文件指定列名和数据类型来列出大容量列别名。

• 在OPENROWSET(BULK...)或语句中INSERTMERGE用作源表时，将数据从数据文件批量导入表中。 有关详细信息，请参阅 使用 BULK INSERT 或 OPENROWSET（BULK...）将数据导入 SQL Server。
• 当 OPENROWSET BULK 选项与语句一 INSERT 起使用时，子 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...)假定如果未指定，则最大长度SQLCHARSQLNCHARSQLBINARY或数据长度不超过 8,000 字节。 如果导入的数据位于包含任何 varchar（max）、 nvarchar（max）或 varbinary（max） 对象超过 8,000 字节的 LOB 数据字段中，则必须使用定义数据字段最大长度的 XML 格式化文件。 若要指定最大长度，请编辑格式文件并声明 MAX_LENGTH 属性。

Note

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

批量导出或导入 SQLXML 文档

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

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

文件元数据函数

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

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

文件名功能

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

返回数据类型为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];

文件路径函数

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

• 当无参数调用时，返回行所属的完整文件路径。
• 当调用参数时，返回路径中与参数指定位置上的万用符匹配的部分路径。 例如，参数值1会返回路径中与第一个万用符匹配的部分。

返回数据类型为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 将文件数据批量插入 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 存储位置配置为外部数据源。 这需要使用共享访问签名的数据库范围的凭据，该签名通过用户数据库中的主密钥进行加密。

-- 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）