导入或导出数据的格式文件 (SQL Server)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Analytics Platform System (PDW)

当向 SQL Server 表中批量导入数据或从该表中批量导出数据时，可以使用格式化文件存储批量导入数据或批量导出数据所需的所有格式信息。 这包括数据文件中相对于该表的各字段的格式信息。

SQL Server 支持两种格式化文件：XML 格式化文件和非 XML 格式化文件。 XML 格式化文件和非 XML 格式化文件在一个数据文件中包含每个字段的说明，并且 XML 格式化文件还包含相应表列的说明。 通常，XML 与非 XML 格式化文件可以互换。 但是，建议您为新的格式化文件使用 XML 语法，因为与非 XML 格式化文件相比，格式化文件具有多项优点。 有关更多信息，请参阅 XML 格式化文件 (SQL Server)。

注意

Azure Synapse Analytics不支持此语法（包括批量插入）。 在 Azure Synapse Analytics 和其他云数据库平台集成中，通过 Azure 数据工厂中的 COPY 语句或使用 T-SQL 语句（如 COPY INTO）和 PolyBase 完成数据移动。

格式化文件的优点

格式化文件是一个灵活的系统，用户只需进行极少的编辑甚至无需编辑即可编写出兼容其他数据格式的数据文件，或从其他软件读取数据文件。

你可以批量导入数据，而不必在数据文件中添加数据、删除不需要的数据或者对现有数据重新排序。 当数据文件中的字段和表中的列存在不匹配的情况时，格式化文件能够发挥作用。

格式化文件的示例

下面的示例说明了非 XML 格式化文件和 XML 格式化文件的布局。 这些格式化文件对应于 HumanResources.myTeam 示例数据库中的 AdventureWorks2022 表。 该表包含四列： EmployeeID、 Name、 Title和 ModifiedDate。

注意

有关该表以及如何创建该表的信息，请参阅 HumanResources.myTeam 示例表 (SQL Server)。

A. 使用非 XML 格式化文件

下面的非 XML 格式化文件为 HumanResources.myTeam 表使用 SQL Server 本机数据格式。 此格式化文件是用下面的 bcp 命令创建的。

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

bcp 命令的默对象是使用 Windows 身份验证的 SQL Server 的本地默认实例。 可根据需要指定其他实例和登录信息，有关详细信息，请参阅 bcp 实用工具。 例如，要指定使用 Windows 身份验证的远程服务器命名实例，请使用：

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

此格式化文件的内容如下所示，开头是 SQL Server 的主版本号，然后是表元数据信息。

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

有关详细信息，请参阅使用非 XML 格式化文件 (SQL Server)。

B. 使用 XML 格式化文件

下面的 XML 格式化文件为 HumanResources.myTeam 表使用 SQL Server 本机数据格式。 此格式化文件是用下面的 bcp 命令创建的。

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.xml -x -n -T

格式化文件包含：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="NativePrefix" LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

有关更多信息，请参阅 XML 格式化文件 (SQL Server)。

何时需要使用格式化文件？

以下情况通常需要使用格式化文件：

  • 使用 INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句时。

  • 使用 bcp 或 BULK INSERT 的复杂批量导入情况。

  • 具有不同架构的多个表使用同一数据文件作为数据源。

  • 数据文件中的字段数不同于目标表中的列数；例如：

    • 目标表中至少包含一个定义了默认值或允许为 NULL 的列。
    • 用户对目标表的一个或多个列不具有 SELECT/INSERT 权限。
    • 具有不同架构的两个或多个表使用同一个数据文件。

  • 数据文件和表的列顺序不同。

  • 数据文件列的终止字符或前缀长度不同。

注意

在缺少格式化文件的情况下，如果 bcp 命令指定了数据格式开关） -n、 -c、 -w或 -N）或 BULK INSERT 操作指定了 DATAFILETYPE 选项，那么指定的数据格式将用作解释数据文件字段的默认方法。

相关内容