创建格式化文件 (SQL Server)Create a Format File (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 into a SQL ServerSQL Server table or bulk export data from a table, you can use a format file to 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 programs.

SQL ServerSQL Server 支持两种类型的格式化文件:非 XML 格式和 XML 格式。support two types of format file: non-XML format and XML format. 非 XML 格式是 SQL ServerSQL Server早期版本支持的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL ServerSQL Server.

通常,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.

备注

读取格式化文件所用的 bcp 实用工具 (Bcp.exe) 的版本必须与创建格式化文件所用的版本相同或更高。The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or later than the version used to create the format file. 例如,SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp 可以读取由 SQL Server 2008SQL Server 2008 bcp 生成的 10.0 版格式化文件,但 SQL Server 2008SQL Server 2008 bcp 无法读取由 SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp 生成的 11.0 版格式化文件 。For example, SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp can read a version 10.0 format file, which is generated by SQL Server 2008SQL Server 2008 bcp, but SQL Server 2008SQL Server 2008 bcp cannot read a version 11.0 format file, which is generated by SQL Server 2012 (11.x)SQL Server 2012 (11.x) bcp.

本主题说明了如何使用 bcp 实用工具 为特定表创建格式化文件。This topic describes how to use the bcp utility to create a format file for a particular table. 格式化文件基于指定的数据类型选项( -n-c-w-N)以及表或视图分隔符。The format file is based on the data-type option specified (-n, -c, -w,or -N) and the table or view delimiters.

创建非 XML 格式化文件Creating a Non-XML Format File

若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format 选项还需要 -f 选项,例如:The format option also requires the -f option, such as:

bcp table_or_view format nul -fformat_file_namebcp table_or_view format nul -fformat_file_name

备注

为区分非 XML 格式化文件,我们建议使用 .fmt 作为文件扩展名,例如 MyTable.fmt。To distinguish a non-XML format file, we recommend that you use .fmt as the file name extension, for example, MyTable.fmt.

有关非 XML 格式化文件结构和字段的信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。For information about the structure and fields of non-XML format files, see Non-XML Format Files (SQL Server).

示例Examples

本部分包含以下示例,演示如何使用 bcp 命令创建非 XML 格式化文件:This section contains the following examples that show how to use bcp commands to create a non-XML format file:

  • A.A. 为本机数据创建非 XML 格式化文件Creating a non-XML format file for native data

  • B.B. 为字符数据创建非 XML 格式化文件Creating a non-XML format file for character data

  • C.C. 为 Unicode 本机数据创建非 XML 格式化文件Creating a non-XML format file for Unicode native data

  • D.D. 为 Unicode 字符数据创建非 XML 格式化文件Creating a non-XML format file for Unicode character data

  • F.F. 使用具有代码页选项的格式化文件Using a format file with the code page option

本示例使用 HumanResources.Department 示例数据库中的 AdventureWorks2012AdventureWorks2012 表。The examples use the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 sample database. HumanResources.Department 表包含四列: DepartmentIDNameGroupNameModifiedDateThe HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

A.A. 为本机数据创建非 XML 格式化文件Creating a non-XML format file for native data

以下示例将为 Department-n.xml表创建 XML 格式化文件 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department-n.xml, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式化文件使用本机数据类型。The format file uses native data types. 在命令之后显示生成的格式化文件的内容。The contents of the generated format file are presented after the command.

bcp 命令包含以下限定符。The bcp command contains the following qualifiers.

限定符Qualifiers 描述Description
formatnul-f format_fileformatnul-f format_file 指定非 XML 格式化文件。Specifies the non-XML format file.
-n-n 指定本机数据类型。Specifies native data types.
-T-T 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,则必须指定 -U-P 才能成功登录。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示符下,输入以下 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt  

生成的格式化文件 Department-n.fmt包含以下信息:The generated format file, Department-n.fmt, contains the following information:

12.0  
4  
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""  
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS  
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS  
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""  

有关详细信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。For more information, see Non-XML Format Files (SQL Server).

B.B. 为字符数据创建非 XML 格式化文件Creating a non-XML format file for character data

以下示例将为 Department.fmt表创建 XML 格式化文件 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department.fmt, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式化文件使用字符数据格式和非默认字段终止符 (,)。The format file uses character data formats and a non-default field terminator (,). 在命令之后显示生成的格式化文件的内容。The contents of the generated format file are presented after the command.

bcp 命令包含以下限定符。The bcp command contains the following qualifiers.

限定符Qualifiers 描述Description
formatnul-f format_fileformatnul-f format_file 指定非 XML 格式化文件。Specifies a non-XML format file.
-c-c 指定字符数据。Specifies character data.
-T-T 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,则必须指定 -U-P 才能成功登录。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示符下,输入以下 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T  

生成的格式化文件 Department-c.fmt包含以下信息:The generated format file, Department-c.fmt, contains the following information:

12.0  
4  
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""  
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS  
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS  
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""  

有关详细信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。For more information, see Non-XML Format Files (SQL Server).

C.C. 为 Unicode 本机数据创建非 XML 格式化文件Creating a non-XML format file for Unicode native data

若要为 HumanResources.Department 表中的 Unicode 本机数据创建非 XML 格式化文件,请使用以下命令:To create a non-XML format file for Unicode native data for the HumanResources.Department table, use the following command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt  

有关如何使用 Unicode 本机数据的详细信息,请参阅使用 Unicode 本机格式导入或导出数据 (SQL Server)For more information about how to use Unicode native data, see Use Unicode Native Format to Import or Export Data (SQL Server).

D.D. 为 Unicode 字符数据创建非 XML 格式化文件Creating a non-XML format file For Unicode character data

若要为 HumanResources.Department 表中使用默认终止符的 Unicode 字符数据创建非 XML 格式化文件,请使用以下命令:To create a non-XML format file for Unicode character data for the HumanResources.Department table that uses default terminators, use the following command:

bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt  

有关如何使用 Unicode 字符数据的详细信息,请参阅使用 Unicode 字符格式导入或导出数据 (SQL Server)For more information about how to use Unicode character data, see Use Unicode Character Format to Import or Export Data (SQL Server).

F.F. 使用具有代码页选项的格式化文件Using a format file with the code page option

如果使用 bcp 命令(即使用 bcp format)创建格式化文件,排序规则/代码页的相关信息将写入格式化文件。If you create a format file using the bcp command (that is, by using bcp format), information about the collation/code page will be written in the format file.
具有 5 列的表的以下示例格式化文件包括了排序规则。The following example format file for a table with 5 columns includes the collation.

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0          Cyrillic_General_CS_AS  
2  SQLCHAR         0       0       "**\t**"         2     c_1          Cyrillic_General_CS_AS  
3  SQLCHAR         0       3000    "**\t**"         3     c_2          Cyrillic_General_CS_AS  
4  SQLCHAR         0       5       "**\t**"         4     c_3          ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""  
  

如果尝试使用 bcp in -c -C65001 -f format_file ..." 或 "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ..." 将数据导入 SQL ServerSQL Server,有关排序规则/代码页的信息会优先于 65001 选项。If you try to import data into SQL ServerSQL Server using bcp in -c -C65001 -f format_file ..." or "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", information about the collation/code page will have priority over 65001 option.
因此,如要生成格式化文件,必须在开始将数据导回 SQL ServerSQL Server之前,手动从生成的格式化文件中删除排序规则信息。Therefore, if you generate a format file, you must manually delete the collation info from the generated format file before you start importing data back into SQL ServerSQL Server.
以下是不具有排序规则信息的格式化文件的示例。The following is an example of the format file without the collation info.

13.0  
5  
1  SQLCHAR         0       0       "**\t**"         1     c_0              ""  
2  SQLCHAR         0       0       "**\t**"         2     c_1              ""  
3  SQLCHAR         0       3000    "**\t**"         3     c_2              ""  
4  SQLCHAR         0       5       "**\t**"         4     c_3              ""  
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""  

创建 XML 格式化文件Creating an XML Format File

若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format 选项始终要求 -f 选项,若要创建 XML 格式化文件,还必须指定 -x 选项,例如:The format option always requires the -f option, and to create an XML format file, you must also specify the -x option, such as:

bcp table_or_view format nul-f format_file_name -xbcp table_or_view format nul-f format_file_name -x

备注

为区分 XML 格式化文件,我们建议使用 .xml 作为文件扩展名,例如 MyTable.xml。To distinguish an XML format file, we recommend that you use .xml as the file name extension, for example, MyTable.xml.

有关 XML 格式化文件结构和字段的信息,请参阅 XML 格式化文件 (SQL Server)早期版本支持的原始格式。For information about the structure and fields of XML format files, see XML Format Files (SQL Server).

示例Examples

本部分包含以下示例,演示如何使用 bcp 命令创建 XML 格式化文件:This section contains the following examples that show how to use bcp commands to create an XML format file:

  • A.A. 为字符数据创建 XML 格式化文件Creating an XML format file for character data
  • B.B. 为本机数据创建 XML 格式化文件Creating an XML format file for native data

本示例使用 HumanResources.Department 示例数据库中的 AdventureWorks2012AdventureWorks2012 表。The examples use the HumanResources.Department table in the AdventureWorks2012AdventureWorks2012 sample database. HumanResources.Department 表包含四列: DepartmentIDNameGroupNameModifiedDateThe HumanResources.Department table contains four columns: DepartmentID, Name, GroupName, and ModifiedDate.

备注

Adventure Works Cycles 是一家虚构的制造公司,用于演示数据库概念和方案。Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios.

A.A. 为字符数据创建 XML 格式化文件Creating an XML format file for character data

以下示例将为 Department.xml表创建 XML 格式化文件 AdventureWorks2012AdventureWorks2012HumanResources.DepartmentThe following example creates an XML format file, Department.xml, for the AdventureWorks2012AdventureWorks2012HumanResources.Department table. 格式化文件使用字符数据格式和非默认字段终止符 (,)。The format file uses character data formats and a non-default field terminator (,). 在命令之后显示生成的格式化文件的内容。The contents of the generated format file are presented after the command.

bcp 命令包含以下限定符。The bcp command contains the following qualifiers.

限定符Qualifiers 描述Description
formatnul-f format_file -xformatnul-f format_file -x 指定 XML 格式化文件。Specifies the XML format file.
-c-c 指定字符数据。Specifies character data.
-t ,-t , 将逗号 ( , ) 指定为字段终止符。Specifies a comma (,) as the field terminator.

注意:如果数据文件使用默认的字段终止符 (\t),则不需要 -t 开关 。Note: If the data file uses the default field terminator (\t), the -t switch is unnecessary.
-T-T 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,则必须指定 -U-P 才能成功登录。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示符下,输入以下 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T  

生成的格式化文件 Department-c.xml包含以下 XML 元素:The generated format file, Department-c.xml, contains the following XML elements:

<?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="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

有关此格式化文件语法的详细信息,请参阅 XML 格式化文件 (SQL Server)For information about the syntax of this format file, see XML Format Files (SQL Server). 有关字符数据的详细信息,请参阅使用字符格式导入或导出数据 (SQL Server)For information about character data, see Use Character Format to Import or Export Data (SQL Server).

B.B. 为本机数据创建 XML 格式化文件Creating an XML format file for native data

以下示例将为 Department-n.xml表创建 XML 格式化文件 HumanResources.DepartmentThe following example creates an XML format file, Department-n.xml, for the HumanResources.Department table. 格式化文件使用本机数据类型。The format file uses native data types. 在命令之后显示生成的格式化文件的内容。The contents of the generated format file are presented after the command.

bcp 命令包含以下限定符。The bcp command contains the following qualifiers.

限定符Qualifiers 描述Description
formatnul-f format_file -xformatnul-f format_file -x 指定 XML 格式化文件。Specifies the XML format file.
-n-n 指定本机数据类型。Specifies native data types.
-T-T 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,则必须指定 -U-P 才能成功登录。If -T is not specified, you must specify -U and -P to successfully log in.

在 Windows 命令提示符下,输入以下 bcp 命令:At the Windows command prompt, enter the following bcp command:

bcp AdventureWorks2012.HumanResources.Department format nul -x -f Department-n.xml -n -T  

生成的格式化文件 Department-n.xml包含以下 XML 元素:The generated format file, Department-n.xml, contains the following XML elements:

<?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="NativeFixed" LENGTH="2"/>  
  <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="NativeFixed" LENGTH="8"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>  
 </ROW>  
</BCPFORMAT>  

有关此格式化文件语法的详细信息,请参阅 XML 格式化文件 (SQL Server)For information about the syntax of this format file, see XML Format Files (SQL Server). 有关如何使用本机数据的详细信息,请参阅使用本机格式导入或导出数据 (SQL Server)For information about how to use native data, see Use Native Format to Import or Export Data (SQL Server).

将数据字段映射到表列Mapping Data Fields to Table Columns

如同使用 bcp创建一样,格式化文件按顺序说明所有的表列。As created by bcp, a format file describes all the table columns in order. 可以修改格式化文件以重新安排或忽略表列。You can modify a format file to rearrange or omit table rows. 这便于您针对字段未直接映射到表列的数据文件来自定义格式化文件。This lets you customize a format file to a data file whose fields do not map directly to the table columns. 有关详细信息,请参阅以下主题:For more information, see the following topics:

另请参阅See Also

bcp 实用工具 bcp Utility
使用格式化文件将表列映射到数据文件字段 (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
使用格式化文件跳过表列 (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
使用格式化文件跳过数据字段 (SQL Server) Use a Format File to Skip a Data Field (SQL Server)
非 XML 格式化文件 (SQL Server) Non-XML Format Files (SQL Server)
XML 格式化文件 (SQL Server)XML Format Files (SQL Server)