使用格式化文件跳过数据字段 (SQL Server)Use a format file to skip a data field (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 more fields than the number of columns in the table. 本主题说明了通过修改非 XML 和 XML 格式化文件，将表中的列映射到相应的数据字段并忽略额外字段，从而能够使用具有较多字段的数据文件。This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. 有关其他信息，请查看 创建格式化文件 (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 a Non-XML Format File
 ● 创建 XML 格式化文件 ● Creating an XML Format File
 ● 修改 XML 格式化文件 ● Modifying an XML Format File
使用格式化文件导入数据以跳过数据字段Importing Data with a Format File to skip a Data 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

下面的脚本创建一个测试数据库和一个名为 myTestSkipField的表。The script below creates a test database and a table named myTestSkipField. 在 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 myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

示例数据文件Sample Data File

创建一个空文件 D:\BCP\myTestSkipField.bcp 并插入以下数据：Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

创建格式化文件Creating the Format Files

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

  • 将第一个数据字段映射到第一列 PersonID。Map the first data field to the first column, PersonID.
  • 跳过第二个数据字段。Skip the second data field.
  • 将第三个数据字段映射到第二列 FirstName。Map the third data field to the second column, FirstName.
  • 将第四个数据字段映射到第三列 LastName。Map the fourth data field to the third column, LastName.

用于创建格式化文件的最简单方法是使用 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 实用工具 基于 myTestSkipField.fmt的架构生成非 xml 格式化文件 myTestSkipField。The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. 此外，限定符 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

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

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

  1. 复制 FirstName 的整个格式化文件行，并紧接在下一行的 FirstName 后面粘贴它。Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line.
  2. 对于新行和所有后续行，将主机文件字段顺序值增加一。Increase the host file field order value by one for the new row and all subsequent rows.
  3. 增加列数值以反映数据文件中的实际字段数。Increase the number of columns value to reflect the actual number of fields in the data file.
  4. 对于第二个格式化文件行，将服务器列顺序从 2 修改为 0 。Modify the server column order from 2 to 0 for the second format-file row.

比较进行的更改：Compare the changes made:
早于Before

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

AfterAfter

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

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

  • 4 个数据字段4 data fields
  • myTestSkipField.bcp 中的第一个数据字段映射到第一列， myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • myTestSkipField.bcp 中的第二个数据字段未映射到任何列。The second data field in myTestSkipField.bcp is not mapped to any column.
  • myTestSkipField.bcp 中的第三个数据字段映射到第二列， myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • myTestSkipField.bcp 中的第四个数据字段映射到第三列， myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

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

有关详细信息，请查看 XML 格式化文件 (SQL Server) 。Please review XML Format Files (SQL Server) for detailed information. 下面的命令使用 bcp 实用工具 基于 myTestSkipField.xml的架构创建 xml 格式化文件 myTestSkipField。The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. 此外，限定符 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.xml -t, -T

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

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

  1. 复制整个第二个字段，并紧接在下一行的第二个字段后面粘贴它。Copy the entire second field and paste it directly after the second field on the next line.
  2. 对于新 FIELD 和每个后续 FIELD，将“FIELD ID”值增加 1。Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD.
  3. 对于 FirstName和 LastName 将“COLUMN SOURCE”值增加 1，以反映修改的映射。Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

比较进行的更改：Compare the changes made:
早于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="\r\n" MAX_LENGTH="30" 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"/>
 </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="25" 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="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

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

  • 4 个数据字段4 data fields
  • 与 COLUMN 1 对应的 FIELD 1 映射到第一个表列， myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • FIELD 2 不与任何 COLUMN 对应，因此不映射到任何表列。FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • 与 COLUMN 3 对应的 FIELD 3 映射到第二个表列， myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • 与 COLUMN 4 对应的 FIELD 4 映射到第三个表列， myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

使用格式化文件导入数据以跳过数据字段Importing Data with a Format File to skip a Data 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.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

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

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

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.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 myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO  

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

使用 BULK INSERT 和 XML 格式化文件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 myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO  

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

使用 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 myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

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

使用 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 myTestSkipField;
INSERT INTO dbo.myTestSkipField 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

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

另请参阅See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
使用格式化文件跳过表列 (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
使用格式化文件将表列映射到数据文件字段 (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)