格式化文件以导入或导出数据 (SQL Server)Format files to import or export data (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

当向 SQL ServerSQL Server 表中大容量导入数据或从该表中大容量导出数据时,可以使用格式化文件 存储大容量导入数据或大容量导出数据所需的所有格式信息。When you bulk import data into a SQL ServerSQL Server table or bulk export data from a table, you can use a format file to store all the format information that is required to bulk export or bulk import data. 这包括数据文件中相对于该表的各字段的格式信息。This includes format information for each field in a data file relative to that table.

SQL ServerSQL Server 支持两种格式化文件类型:XML 格式和非 XML 格式化文件。supports two types of format files: XML formats and non-XML format files. XML 格式化文件和非 XML 格式化文件在一个数据文件中包含每个字段的说明,并且 XML 格式化文件还包含相应表列的说明。Both non-XML format files and XML format files contain descriptions of every field in a data file, and XML format files also contain descriptions of the corresponding table columns. 通常,XML 与非 XML 格式化文件可以互换。Generally, XML and non-XML format files are interchangeable. 但是,建议您为新的格式化文件使用 XML 语法,因为与非 XML 格式化文件相比,格式化文件具有多项优点。However, we recommend that you use the XML syntax for new format files because they provide several advantages over non-XML format files. 有关详细信息,请参阅 XML 格式化文件 (SQL Server)For more information, see XML Format Files (SQL Server).

格式化文件的优点Benefits of Format Files

  • 为编写数据文件提供了一个灵活的系统,用户只需进行极少的编辑甚至无需编辑即可编写出符合其他数据格式的数据文件,或从其他软件读取数据文件。Provides a flexible system for writing data files that requires little or no editing to comply with other data formats or to read data files from other software.
  • 使您可以大容量导入数据,而不必添加或删除不需要的数据或者重新排列数据文件中现有数据的顺序。Enables you to bulk import data without having to add or delete unnecessary data or to reorder existing data in the data file. 当数据文件中的字段和表中的列存在不匹配的情况时,格式化文件尤其有用。Format files are particularly useful when a mismatch exists between fields in the data file and columns in the table.

格式化文件的示例Examples of Format Files

下面的示例说明了非 XML 格式化文件和 XML 格式化文件的布局。The following examples show the layout of a non-XML format file and of an XML format file. 这些格式化文件对应于 HumanResources.myTeam 示例数据库中的 AdventureWorks2012AdventureWorks2012 表。These format files correspond to the HumanResources.myTeam table in the AdventureWorks2012AdventureWorks2012 sample database. 该表包含四列:EmployeeIDNameTitleModifiedDateThis table contains four columns: EmployeeID, Name, Title, and ModifiedDate.

备注

有关该表以及如何创建该表的信息,请参阅 HumanResources.myTeam 示例表 (SQL Server)For information about this table and how to create it, see HumanResources.myTeam Sample Table (SQL Server).

A.A. 使用非 XML 格式化文件Using a non-XML format file

下面的非 XML 格式化文件为 SQL ServerSQL Server 表使用 HumanResources.myTeam 本机数据格式。The following non-XML format file uses the SQL ServerSQL Server native data format for the HumanResources.myTeam table. 此格式化文件是用下面的 bcp 命令创建的。This format file was created by using the following bcp command.

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Fmt -n -T
The contents of this format file are as follows: 9.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)For more information, see Non-XML Format Files (SQL Server).

B.B. 使用 XML 格式化文件Using an XML format file

下面的 XML 格式化文件为 SQL ServerSQL Server 表使用 HumanResources.myTeam 本机数据格式。The following XML format file uses the SQL ServerSQL Server native data format for the HumanResources.myTeam table. 此格式化文件是用下面的 bcp 命令创建的。This format file was created by using the following bcp command.

bcp AdventureWorks.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T

格式化文件包含:The format file contains:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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)For more information, see XML Format Files (SQL Server).

何时需要使用格式化文件?When Is a Format File Required?

  • INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句始终要求使用格式化文件。An INSERT ... SELECT * FROM OPENROWSET(BULK...) statement always requires a format file.
  • 对于 bcp 或 BULK INSERT,在简单的情况下,请视情况选用格式化文件,在极少数的情况下才必须使用。For bcp or BULK INSERT, in simple situations, using a format file is optional and rarely necessary. 但是,对于复杂的大容量导入情况,通常都会需要格式化文件。However, for complex bulk-import situations, a format file is frequently required.

在以下情况下,必须使用格式化文件:Format files are required if:

  • 具有不同架构的多个表使用同一数据文件作为数据源。The same data file is used as a source for multiple tables that have different schemas.

  • 数据文件中的字段数不同于目标表中的列数;例如:The data file has a different number of fields that the target table has columns; for example:

    • 目标表中至少包含一个定义了默认值或允许为 NULL 的列。The target table contains at least one column for which either a default value is defined or NULL is allowed.
    • 用户不具有对目标表的一个或多个列的 SELECT/INSERT 权限。The users do not have SELECT/INSERT permissions on one or more columns in the table.
    • 具有不同架构的两个或多个表使用同一个数据文件。A single data file is used with two or more tables that have different schemas.
  • 数据文件和表的列顺序不同。The column order is different for the data file and table.

  • 数据文件列的终止字符或前缀长度不同。The terminating characters or prefix lengths differ among the columns of the data file.

备注

在缺少格式化文件的情况下,如果 bcp 命令指定了数据格式开关( -n-c-w-N),或者 BULK INSERT 操作指定了 DATAFILETYPE 选项,那么指定的数据格式将用作解释数据文件字段的默认方法。In the absence of a format file, if a bcp command specifies a data-format switch (-n, -c, -w, or -N) or a BULK INSERT operation specifies the DATAFILETYPE option, the specified data format is used as the default method of interpreting the fields of the data file.

相关任务Related Tasks

另请参阅See Also