使用格式化文件跳过表列 (SQL Server)Use a Format File to Skip a Table Column (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

本文描述当源数据文件中不存在该已跳过列的数据时,如何使用格式化文件跳过导入表列。This article describes how to use a format file to skip importing a table column when the data for the skipped column does not exist in the source data file. 数据文件可包含比目标表中的列数更少的字段,即你可以跳过导入列,前提是目标表中满足以下两个条件中至少一个:A data file can contain fewer fields than the number of columns in the destination table - that is, you can skip importing a column - only if at least one of the following two conditions is true in the destination table:

  • 跳过的列可以为 null。The skipped column is nullable.
  • 跳过的列具有默认值。The skipped column has a default value.

示例表和数据文件Sample table and data file

本文中的示例需要一个处于 dbo 架构下的名为 myTestSkipCol 的表。The examples in this article expect a table named myTestSkipCol under the dbo schema. 可以在示例数据库(例如 WideWorldImportersAdventureWorks)或任何其他数据库中创建此表。You can create this table in a sample database such as WideWorldImporters or AdventureWorks or in any other database. 此表的创建方式如下所示:Create this table as follows:

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol   
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO  

本文中的示例还使用示例数据文件 myTestSkipCol2.datThe examples in this article also use a sample data file, myTestSkipCol2.dat. 虽然目标表包含三个列,但此数据文件仅包含两个字段。This data file contains only two fields, although the destination table contains three columns.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3  

基本步骤Basic steps

可以使用非 XML 格式化文件或 XML 格式化文件跳过表列。You can use a non-XML format file or an XML format file to skip a table column. 这两种情况都可通过两步完成:In both cases, there are two steps:

  1. 使用 bcp 命令行实用工具创建默认格式化文件。Use the bcp command-line utility to create a default format file.

  2. 在文本编辑器中修改默认格式化文件。Modify the default format file in a text editor.

修改后的格式化文件必须将每个现有字段映射到目标表中的相应列。The modified format file must map each existing field to its corresponding column in the destination table. 它还必须指示要跳过哪些列。It must also indicate which table column or columns to skip.

例如,若要将数据从 myTestSkipCol2.dat 批量导入 myTestSkipCol 表,则格式化文件必须将第一个数据字段映射到 Col1,跳过 Col2,并将第二个字段映射到 Col3For example, to bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, skip Col2, and map the second field to Col3.

选项 #1 - 使用非 XML 格式化文件Option #1 - Use a non-XML format file

步骤 #1 - 创建默认非 XML 格式化文件Step #1 - Create a default non-XML format file

在命令提示符下运行以下 bcp 命令为 myTestSkipCol 示例表创建默认非 XML 格式化文件:Create a default non-XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T  

重要

可能必须通过 -S 参数指定要连接的服务器实例的名称。You might have to specify the name of the server instance to which you are connecting with the -S argument. 此外,可能还须通过 -U-P 参数指定用户名和密码。Also, you might have to specify the user name and password with the -U and -P arguments. 有关详细信息,请参阅 bcp UtilityFor more information, see bcp Utility.

以上命令创建了一个非 XML 格式化文件, myTestSkipCol_Default.fmtThe previous command creates a non-XML format file, myTestSkipCol_Default.fmt. 此格式化文件称为 默认格式化文件 ,因为它是 bcp生成的格式。This format file is called a default format file because it is the form generated by bcp. 默认格式化文件说明数据文件字段与表列之间的一一对应关系。A default format file describes a one-to-one correspondence between data-file fields and table columns.

以下截图显示了此示例默认格式化文件中的值。The following screenshot shows the values in this sample default format files.

适用于 myTestSkipCol 的默认的非 XML 格式文件default non-XML format file for myTestSkipCol

备注

有关格式化文件字段的详细信息,请参阅非 XML 格式化文件 (SQL Server)For more information about the format-file fields, see Non-XML Format Files (SQL Server).

步骤 #2 - 修改非 XML 格式化文件Step #2 - Modify a non-XML format file

若要修改默认非 XML 格式化文件,有两种替代方法。To modify a default non-XML format file, there are two alternatives. 两种方法都假定数据文件中没有数据字段,并且不会有数据插入到对应的表列中。Either alternative indicates that the data field does not exist in the data file and that no data is to be inserted into the corresponding table column.

若要跳过某个表列,可编辑默认非 XML 格式化文件并使用以下方法之一修改此文件:To skip a table column, edit the default non-XML format file and modify the file by using one of the following alternative methods:

选项 #1 - 删除行Option #1 - Remove the row

跳过列的首选方法包括以下三个步骤:The preferred method for skipping a column involves the following three steps:

  1. 首先,删除说明源数据文件中丢失字段的任何格式化文件行。First, delete any format-file row that describes a field that is missing from the source data file.
  2. 然后,减小所删除行后的每个格式化文件行的“宿主文件字段顺序”值。Then, reduce the "Host file field order" value of each format-file row that follows a deleted row. 这是为了使“宿主文件字段顺序”值按顺序排列(1 到 n),它反映了数据文件中各个数据字段的实际位置。The goal is sequential "Host file field order" values, 1 through n, that reflect the actual position of each data field in the data file.
  3. 最后,减小“列数”字段中的值以反映数据文件中的实际字段数。Finally, reduce the value in the "Number of columns" field to reflect the actual number of fields in the data file.

下面的示例基于 myTestSkipCol 表的默认格式化文件。The following example is based on the default format file for the myTestSkipCol table. 此修改过的格式化文件将第一个数据字段映射到 Col1,并跳过 Col2将第二个数据字段映射到 Col3This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. 已删除 Col2 的行。The row for Col2 has been deleted. 第一个字段后的分隔符也已从 \t 更改为 ,The delimiter after the first field has also been changed from \t to ,.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

选项 #2 - 修改行定义Option #2 - Modify the row definition

若要跳过某个表列,也可以修改与表列对应的格式化文件行的定义。Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. 在此格式化文件行中,“前缀长度”、“宿主文件数据长度”和“服务器列顺序”值必须设置为 0,In this format-file row, the "prefix length," "host file data length," and "server column order" values must be set to 0. 并且“终止符”和“列排序规则”字段必须设置为 ""(即设置为空值或 NULL 值)。Also, the "terminator" and "column collation" fields must be set to "" (that is, to an empty or NULL value). “服务器列名”值必须为非空白字符串,但不一定为实际列名。The "server column name" value requires a non-blank string, though the actual column name is not necessary. 其余格式字段必须为它们的默认值。The remaining format fields require their default values.

下面的示例也是从 myTestSkipCol 表的默认格式化文件派生出来的。The following example is also derived from the default format file for the myTestSkipCol table.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

非 XML 格式化文件的示例Examples with a non-XML format file

下面的示例基于本文前述的 myTestSkipCol 示例表和 myTestSkipCol2.dat 示例数据文件。The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

使用 BULK INSERTUsing BULK INSERT

此示例使用前面部分中所述创建的任一已修改非 XML 格式化文件。This example works by using either of the modified non-XML format files created as described in the preceding section. 在此例中,修改过的格式化文件名为 myTestSkipCol2.fmtIn this example, the modified format file is named myTestSkipCol2.fmt. 若要在 SSMS 中使用 BULK INSERT 批量导入 myTestSkipCol2.dat 数据文件,请运行以下代码。To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in SSMS, run the following code. 更新计算机上示例文件位置的文件系统路径。Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  
BULK INSERT myTestSkipCol   
   FROM 'C:\myTestSkipCol2.dat'   
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO  

选项 #2 - 使用 XML 格式化文件Option #2 - Use an XML format file

步骤 #1 - 创建默认 XML 格式化文件Step #1 - Create a default XML format file

在命令提示符下运行以下 bcp 命令为 myTestSkipCol 示例表创建默认 XML 格式化文件:Create a default XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T  

重要

可能必须通过 -S 参数指定要连接的服务器实例的名称。You might have to specify the name of the server instance to which you are connecting with the -S argument. 此外,可能还须通过 -U-P 参数指定用户名和密码。Also, you might have to specify the user name and password with the -U and -P arguments. 有关详细信息,请参阅 bcp UtilityFor more information, see bcp Utility.

以上命令将创建 XML 格式化文件 myTestSkipCol_Default.xmlThe previous command creates an XML format file, myTestSkipCol_Default.xml. 此格式化文件称为 默认格式化文件 ,因为它是 bcp生成的格式。This format file is called a default format file because it is the form generated by bcp. 默认格式化文件说明数据文件字段与表列之间的一一对应关系。A default format file describes a one-to-one correspondence between data-file fields and table columns.

<?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="\t" MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

备注

有关 XML 格式化文件结构的详细信息,请参阅 XML 格式化文件 (SQL Server)For information about the structure of XML format files, see XML Format Files (SQL Server).

步骤 #2 - 修改 XML 格式化文件Step #2 - Modify an XML format file

下面是将跳过 Col2 的已修改 XML 格式化文件 myTestSkipCol2.xmlHere is the modified XML format file, myTestSkipCol2.xml, which skips Col2. Col2FIELDROW 条目已删除,并且条目已重新编号。The FIELD and ROW entries for Col2 have been removed and the entries have been renumbered. 第一个字段后的分隔符也已从 \t 更改为 ,The delimiter after the first field has also been changed from \t to ,.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

XML 格式化文件的示例Examples with an XML format file

下面的示例基于本文前述的 myTestSkipCol 示例表和 myTestSkipCol2.dat 示例数据文件。The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

为将数据从 myTestSkipCol2.dat 导入 myTestSkipCol 表,示例使用修改过的 XML 格式化文件 myTestSkipCol2.xmlTo import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the modified XML format file, myTestSkipCol2.xml.

对视图使用 BULK INSERTUsing BULK INSERT with a view

使用 XML 格式化文件时,你无法在通过 bcp 命令或 BULK INSERT 语句直接向表导入内容时跳过列。With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. 但是,您可以向表中除最后一列的所有列导入。However, you can import into all but the last column of a table. 如果必须跳过最后一列以外的任何列,必须创建仅包含数据文件所含列的目标表视图。If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file. 然后,您可以将此文件中的数据大容量导入此视图。Then, you can bulk import data from that file into the view.

下面的示例在 myTestSkipCol 表上创建 v_myTestSkipCol 视图。The following example creates the v_myTestSkipCol view on the myTestSkipCol table. 此视图跳过第二表列 Col2This view skips the second table column, Col2. 然后此例使用 BULK INSERTmyTestSkipCol2.dat 数据文件导入此视图。The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

在 SSMS 中,运行以下代码。In SSMS, run the following code. 更新计算机上示例文件位置的文件系统路径。Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO  
  
BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO  

使用 OPENROWSET(BULK...)Using OPENROWSET(BULK...)

若要使用 XML 格式化文件通过 OPENROWSET(BULK...) 跳过表列,必须提供选择列表以及目标表中列的显式列表,如下所示:To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide an explicit list of columns in the select list and also in the target table, as follows:

```sql
INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...) 
```

下面的示例使用 OPENROWSET 大容量行集提供程序和 myTestSkipCol2.xml 格式化文件。The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. 此示例将 myTestSkipCol2.dat 数据文件大容量导入至 myTestSkipCol 表。The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. 语句中包含了需要提供的选择列表以及目标表中列的显式列表。The statement contains an explicit list of columns in the select list and also in the target table, as required.

在 SSMS 中,运行以下代码。In SSMS, run the following code. 更新计算机上示例文件位置的文件系统路径。Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'    
       ) as t1 ;  
GO  

另请参阅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 Data Field (SQL Server)
使用格式化文件将表列映射到数据文件字段 (SQL Server) Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
使用格式化文件批量导入数据 (SQL Server)Use a Format File to Bulk Import Data (SQL Server)