使用格式化文件将表列映射到数据文件字段 (SQL Server)Use a format file to map table columns to data-file fields (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

数据文件中包含的字段的排列顺序可能不同于表中相应列的顺序。A data file can contain fields arranged in a different order from the corresponding columns in the table. 本主题介绍了非 XML 格式化文件和 XML 格式化文件,它们经过修改可容纳字段排列顺序不同于表列顺序的数据文件。This topic presents both non-XML and XML format files that have been modified to accommodate a data file whose fields are arranged in a different order from the table columns. 修改后的格式化文件可将数据字段映射到与之相应的表列。The modified format file maps the data fields to their corresponding table columns. 有关其他信息,请查看 创建格式化文件 (SQL Server)Please review Create a Format File (SQL Server) for additional information.

轮廓Outline
示例测试条件Example Test Conditions
 ● 示例表 ● Sample Table
 ● 示例数据文件 ● Sample Data File
创建格式化文件Creating the Format Files
 ● 创建非 XML 格式化文件 ● Creating a Non-XML Format File
 ● 修改非 XML 格式化文件 ● Modifying the Non-XML Format File
 ● 创建 XML 格式化文件 ● Creating an XML Format File
 ● 修改 XML 格式化文件 ● Modifying the XML Format File
使用格式化文件导入数据以将表列映射到数据文件字段Importing Data with a Format File to Map Table Columns to Data-File Field
 ● 使用 bcp 和非 XML 格式化文件 ● Using bcp and Non-XML Format File
 ● 使用 bcp 和 XML 格式化文件 ● Using bcp and XML Format File
 ● 使用 BULK INSERT 和非 XML 格式化文件 ● Using BULK INSERT and Non-XML Format File
 ● 使用 BULK INSERT 和 XML 格式化文件 ● Using BULK INSERT and XML Format File
 ● 使用 OPENROWSET(BULK...) 和非 XML 格式化文件 ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● 使用 OPENROWSET(BULK...) 和 XML 格式化文件 ● Using OPENROWSET(BULK...) and XML Format File

备注

可以使用非 XML 或 XML 格式化文件将数据文件批量导入表中,方法是使用 bcp 实用工具命令、BULK INSERT 语句或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 语句。Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. 有关详细信息,请参阅使用格式化文件批量导入数据 (SQL Server)For more information, see Use a Format File to Bulk Import Data (SQL Server).

示例测试条件Example Test Conditions

本主题中修改的格式化文件示例基于下面定义的表和数据文件。The examples of modified format files in this topic are based on the table and data file defined below.

示例表Sample Table

下面的脚本创建一个测试数据库和一个名为 myRemap的表。The script below creates a test database and a table named myRemap. 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

示例数据文件Sample Data File

下面的数据按照与表 FirstName 中的呈现顺序相反的顺序来呈现 LastNamemyRemapThe data below presents FirstName and LastName in the reverse order as presented in the table myRemap. 使用记事本创建一个空文件 D:\BCP\myRemap.bcp 并插入以下数据:Using Notepad, create an empty file D:\BCP\myRemap.bcp and insert the following data:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

创建格式化文件Creating the Format Files

若要将数据从 myRemap.bcp 大容量导入至 myRemap 表,则该格式化文件必须进行下列操作:To bulk import data from myRemap.bcp into the myRemap table, the format file must do the following:

  • 将第一个数据字段映射到第一列 PersonIDMap the first data field to the first column, PersonID.
  • 将第二个数据字段映射到第三列 LastNameMap the second data field to the third column, LastName.
  • 将第三个数据字段映射到第二列 FirstNameMap the third data field to the second column, FirstName.
  • 将第四个数据字段映射到第四列 GenderMap the fourth data field to the fourth column, Gender.

用于创建格式化文件的最简单方法是使用 bcp 实用工具The simplest method to create the format file is by using the bcp utility. 首先,从现有表创建基本格式化文件。First, create a base format file from the existing table. 其次,修改基本格式化文件以反映实际数据文件。Second, modify the base format file to reflect the actual data file.

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

有关详细信息,请查看 非 XML 格式化文件 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令使用 bcp 实用工具 基于 myRemap.fmt的架构生成非 xml 格式化文件 myRemapThe following command will use the bcp utility to generate a non-xml format file, myRemap.fmt, based on the schema of myRemap. 此外,限定符 c 用于指定字符数据, t, 用于将逗号指定为字段终止符,而 T 用于指定使用集成安全性的信任连接。In addition, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

修改非 XML 格式化文件 Modifying the Non-XML Format File

有关术语,请参阅 非 XML 格式化文件的结构See Structure of Non-XML Format Files for terminology. 在记事本中打开 D:\BCP\myRemap.fmt 并执行以下修改:Open D:\BCP\myRemap.fmt in Notepad and perform the following modifications:

  1. 重新排列格式化文件行的顺序,以便这些行的顺序与 myRemap.bcp中的数据相同。Re-arrange the order of the format-file rows so that the rows are in the same order as the data in myRemap.bcp.
  2. 确保主机文件字段顺序值是顺序的。Ensure the host file field order values are sequential.
  3. 确保最后一个格式化文件行后面有一个回车符。Ensure there is a carriage return after the last format-file row.

比较更改:Compare the changes:
早于Before

13.0
4
1       SQLCHAR 0       7       ","      1     PersonID               ""
2       SQLCHAR 0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR 0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

AfterAfter

13.0
4
1       SQLCHAR 0       7       ","      1     PersonID               ""
2       SQLCHAR 0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR 0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR 0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

修改的格式化文件现在可反映:The modified format file now reflects:

  • myRemap.bcp 中的第一个数据字段映射到第一列, myRemap.. PersonIDThe first data field in myRemap.bcp is mapped to the first column, myRemap.. PersonID
  • myRemap.bcp 中的第二个数据字段映射到第三列, myRemap.. LastNameThe second data field in myRemap.bcp is mapped to the third column, myRemap.. LastName
  • myRemap.bcp 中的第三个数据字段映射到第二列, myRemap.. FirstNameThe third data field in myRemap.bcp is mapped to the second column, myRemap.. FirstName
  • myRemap.bcp 中的第四个数据字段映射到第四列, myRemap.. GenderThe fourth data field in myRemap.bcp is mapped to the fourth column, myRemap.. Gender

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

有关详细信息,请查看 XML 格式化文件 (SQL Server)Please review XML Format Files (SQL Server) for detailed information. 下面的命令使用 bcp 实用工具 基于 myRemap.xml的架构创建 xml 格式化文件 myRemapThe following command will use the bcp utility to create an xml format file, myRemap.xml, based on the schema of myRemap. 此外,限定符 c 用于指定字符数据, t, 用于将逗号指定为字段终止符,而 T 用于指定使用集成安全性的信任连接。In addition, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. x 限定符必须用于生成基于 XML 的格式化文件。The x qualifier must be used to generate an XML-based format file. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

修改 XML 格式化文件 Modifying the XML Format File

有关术语,请参阅 XML 格式化文件的架构语法See Schema Syntax for XML Format Files for terminology. 在记事本中打开 D:\BCP\myRemap.xml 并执行以下修改:Open D:\BCP\myRemap.xml in Notepad and perform the following modifications:

  1. 在格式化文件中声明 <FIELD> 元素的顺序是这些字段在数据文件中的显示顺序,因此颠倒具有 ID 属性 2 和 3 的 <FIELD> 元素的顺序。The order in which the <FIELD> elements are declared in the format file is the order in which those fields appear in the data file, thus reverse the order for the <FIELD> elements with ID attributes 2 and 3.
  2. 确保 <FIELD> ID 属性值是顺序的。Ensure the <FIELD> ID attribute values are sequential.
  3. <ROW> 元素中 <COLUMN> 元素的顺序定义了其在批量操作中返回的顺序。The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. XML 格式化文件为每个 <COLUMN> 元素分配了一个本地名称,该名称与批量导入操作的目标表中的列没有关系。The XML format file assigns each <COLUMN> element a local name that has no relationship to the column in the target table of a bulk import operation. <COLUMN> 元素的顺序与 <RECORD> 定义中的 <FIELD> 元素的顺序无关。The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a <RECORD> definition. 每个 <COLUMN> 元素对应一个 <FIELD> 元素(其 ID 在 <COLUMN> 元素的 SOURCE 属性中指定)。Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element). 因此,<COLUMN> SOURCE 的值是需要修订的唯一属性。Thus, the values for <COLUMN> SOURCE are the only attributes that require revision. 颠倒 <COLUMN> SOURCE 属性 2 和 3 的顺序。Reverse the order for <COLUMN> SOURCE attributes 2 and 3.

比较更改Compare the changes
早于Before

\<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  \<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  \<COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

AfterAfter

\<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  \<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  \<COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

修改的格式化文件现在可反映:The modified format file now reflects:

  • 与 COLUMN 1 对应的 FIELD 1 映射到第一个表列, myRemap.. PersonIDFIELD 1, which corresponds to COLUMN 1, is mapped to the first table column, myRemap.. PersonID
  • 与 COLUMN 2 对应的 FIELD 2 重新映射到第三个表列, myRemap.. LastNameFIELD 2, which corresponds to COLUMN 2, is re-mapped to the third table column, myRemap.. LastName
  • 与 COLUMN 3 对应的 FIELD 3 重新映射到第二个表列, myRemap.. FirstNameFIELD 3, which corresponds to COLUMN 3, is re-mapped to the second table column, myRemap.. FirstName
  • 与 COLUMN 4 对应的 FIELD 4 映射到第四个表列, myRemap.. GenderFIELD 4, which corresponds to COLUMN 4, is mapped to the fourth table column, myRemap.. Gender

使用格式化文件导入数据以将表列映射到数据文件字段Importing Data with a Format File to Map Table Columns to Data-File Field

下面的示例使用上面创建的数据库、数据文件和格式化文件。The examples below use the database, datafile, and format files created above.

使用 bcp非 XML 格式化文件Using bcp and Non-XML Format File

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

使用 bcpXML 格式化文件Using bcp and XML Format File

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

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

使用 BULK INSERT非 XML 格式化文件Using BULK INSERT and Non-XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap   
   FROM 'D:\BCP\myRemap.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

使用 BULK INSERTXML 格式化文件Using BULK INSERT and XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
BULK INSERT dbo.myRemap   
   FROM 'D:\BCP\myRemap.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myRemap.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

使用 OPENROWSET(BULK...)非 XML 格式化文件Using OPENROWSET(BULK...) and Non-XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

使用 OPENROWSET(BULK...)XML 格式化文件Using OPENROWSET(BULK...) and XML Format File

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myRemap;
INSERT INTO dbo.myRemap 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myRemap.bcp',
        FORMATFILE = 'D:\BCP\myRemap.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myRemap;

另请参阅See Also

bcp Utility bcp Utility
使用格式化文件跳过表列 (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)