通过

使用格式化文件跳过表列 （SQL Server）

本主题介绍格式化文件。 当数据文件中不存在字段时，可以使用格式化文件跳过导入表列。 仅当跳过的列可以为 null 且/或具有默认值时，数据文件才能包含的字段数少于表中的列数。

示例表和数据文件

以下示例要求在 dbo 架构下的 AdventureWorks2012 示例数据库中命名myTestSkipCol的表。 按如下所示创建此表：

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

以下示例使用一个示例数据文件， myTestSkipCol2.dat该文件仅包含两个字段，尽管相应的表包含三列：

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

若要从myTestSkipCol2.dat导入数据到myTestSkipCol表，格式化文件必须将第一个数据字段映射到Col1，将第二个字段映射到Col3，跳过Col2。

使用非 XML 格式化文件

可以修改非 XML 格式化文件以跳过表列。 通常，这涉及到使用 bcp 实用工具创建默认的非 XML 格式化文件和修改文本编辑器中的默认文件。 修改后的格式化文件必须将每个现有字段映射到其相应的表列，并指示要跳过的表列。 存在两种修改默认非 XML 数据文件的替代方法。 任一替代方法都表示数据文件中不存在数据字段，并且不会将数据插入相应的表列。

创建默认的非 XML 格式化文件

本主题使用默认的非 XML 格式文件，该文件是通过以下 bcp 命令为myTestSkipCol示例表创建的。

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

上一个命令创建非 XML 格式化文件 myTestSkipCol_Default.fmt。 此格式化文件称为 默认格式化文件 ，因为它是 bcp 生成的表单。 通常，默认格式化文件描述数据文件字段和表列之间的一对一对应关系。

重要

可能需要指定要连接到的服务器实例的名称。 此外，可能需要指定用户名和密码。 有关详细信息，请参阅 bcp Utility。

下图显示了此示例默认格式化文件中的值。 此图还显示了每个格式化文件字段的名称。

myTestSkipCol 的默认非 XML 格式化文件

注释

有关格式化文件字段的详细信息，请参阅非 XML 格式化文件（SQL Server）。

修改非 XML 格式化文件的方法

若要跳过表列，请编辑默认的非 XML 格式化文件，并使用下列替代方法之一修改文件：

  • 首选方法涉及三个基本步骤。 首先，删除描述数据文件中缺少的字段的任何格式化文件行。 然后，减少删除行后每个格式化文件行的“主机文件字段顺序”值。 目标是顺序“主机文件字段顺序”值（1 到 n）来反映数据文件中每个数据字段的实际位置。 最后，减少“列数”字段中的值，以反映数据文件中实际字段数。

    以下示例基于在本主题前面的“创建默认非 XML 格式化文件”中创建的 myTestSkipCol 表的默认格式化文件。 此修改后的格式化文件将第一个数据字段映射到第一个数据字段 Col1，跳过 Col2，并将第二个数据字段映射到 Col3。 Col2 行已被删除。 其他修改以粗体表示：

    9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

  • 或者，若要跳过表列，可以修改与表列对应的格式化文件行的定义。 在此格式化文件行中，“前缀长度”、“主机文件数据长度”和“服务器列顺序”值必须设置为 0。 此外，“终止符”和“列排序规则”字段必须设置为“”（NULL）。

    “服务器列名称”值需要非空字符串，但不需要实际列名。 其余格式字段需要其默认值。

    以下示例也派生自表的默认格式化文件 myTestSkipCol 。 必须为 0 或 NULL 的值以粗体表示。

    9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       00""0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

例子

以下示例也基于在本主题前面的“示例表和数据文件”中创建的myTestSkipCol示例表和myTestSkipCol2.dat示例数据文件。

使用 BULK INSERT

此示例通过使用本主题前面“修改非 XML 格式化文件的方法”中创建的任一修改后的非 XML 格式化文件来进行。 在此示例中，修改后的格式化文件命名 C:\myTestSkipCol2.fmt为 。 若要使用 BULK INSERT 批量导入 myTestSkipCol2.dat 数据文件，请在 SQL Server Management Studio 查询编辑器中执行以下代码：

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

使用 XML 格式文件

使用 XML 格式化文件，在使用 bcp 命令或 BULK INSERT 语句直接导入表中时，不能跳过列。 但是，您可以导入表的所有列，除了最后一列。 如果必须跳过除最后一列之外的任何列，则必须创建一个目标表的视图，该视图仅包含数据文件中的列。 然后，可以将该文件中的数据批量导入到视图中。

若要使用 XML 格式化文件通过 OPENROWSET（BULK...）跳过表列，必须在选择列表中和目标表中提供列的显式列表，如下所示：

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

创建默认 XML 格式化文件

修改后的格式化文件的示例基于 myTestSkipCol 本主题前面的“示例表和数据文件”中创建的示例表和数据文件。 以下 bcp 命令为 myTestSkipCol 表创建默认 XML 格式化文件：

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

生成的默认非 XML 格式化文件描述数据文件字段和表列之间的一对一对应关系，如下所示：

<?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）。

例子

本节中的示例使用在本主题前面的“示例表和数据文件”中创建的 myTestSkipCol 示例表和 myTestSkipCol2.dat 示例数据文件。 若要将数据从myTestSkipCol2.datmyTestSkipCol表中导入，这些示例使用以下修改后的 XML 格式化文件myTestSkipCol2-x.xml。 这基于本主题前面的“创建默认 XML 格式化文件”中创建的格式化文件。

<?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>

使用 OPENROWSET（BULK...）

以下示例使用 OPENROWSET 大容量行集提供程序和 myTestSkipCol2.xml 格式化文件。 该示例将数据文件myTestSkipCol2.dat批量导入myTestSkipCol表中。 该语句包含选定列表中的列的显式列表，也包含目标表中的显式列表（根据需要）。

在 SQL Server Management Studio 查询编辑器中，执行以下代码：

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

在视图中执行 BULK IMPORT

以下示例在 myTestSkipCol 表上创建 v_myTestSkipCol。 此视图跳过第二个表列。 Col2 该示例使用 BULK INSERT 将 myTestSkipCol2.dat 数据文件导入到此视图中。

在 SQL Server Management Studio 查询编辑器中，执行以下代码：

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

另请参阅

bcp 实用工具BULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)使用格式化文件跳过数据字段 (SQL Server)使用格式化文件将表列映射到 Data-File 字段 (SQL Server)使用格式化文件批量导入数据 (SQL Server)

  • Last updated on