使用格式化文件跳过数据字段 (SQL Server)

项目
2025/01/02

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

数据文件所包含的字段数可能大于表中的列数。本主题说明了通过修改非 XML 和 XML 格式化文件，将表中的列映射到相应的数据字段并忽略额外字段，从而能够使用具有较多字段的数据文件。有关其他信息，请查看创建格式化文件 (SQL Server) 。

备注

可以使用非 XML 或 XML 格式化文件将数据文件批量导入表中，方法是使用 bcp 实用工具命令、BULK INSERT 语句或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 语句。有关详细信息，请参阅使用格式化文件批量导入数据 (SQL Server)。

备注

Azure Synapse Analytics不支持此语法（包括批量插入）。在 Azure Synapse Analytics 和其他云数据库平台集成中，通过 Azure 数据工厂中的 COPY 语句或使用 T-SQL 语句（如 COPY INTO）和 PolyBase 完成数据移动。

示例测试条件

本主题中修改的格式化文件示例基于下面定义的表和数据文件。

示例表

下面的脚本创建一个测试数据库和一个名为 myTestSkipField的表。在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

SQL

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

示例数据文件

创建一个空文件 D:\BCP\myTestSkipField.bcp 并插入以下数据：

输出

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

创建格式化文件

若要将数据从 myTestSkipField.bcp 大容量导入至 myTestSkipField 表，则该格式化文件必须进行下列操作：

将第一个数据字段映射到第一列 PersonID。
跳过第二个数据字段。
将第三个数据字段映射到第二列 FirstName。
将第四个数据字段映射到第三列 LastName。

用于创建格式化文件的最简单方法是使用 bcp 实用工具。首先，从现有表创建基本格式化文件。其次，修改基本格式化文件以反映实际数据文件。

创建非 XML 格式化文件

有关详细信息，请查看非 XML 格式化文件 (SQL Server)。下面的命令基于的架构使用 bcp 实用工具 myTestSkipField.fmt生成非 XML 格式化文件 myTestSkipField。此外，限定符 c 用于指定字符数据， t, 用于将逗号指定为字段终止符，而 T 用于指定使用集成安全性的信任连接。在命令提示符处输入以下命令：

Windows 命令提示符

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

修改非 XML 格式化文件

有关术语，请查看非 XML 格式化文件的结构。在记事本中打开 D:\BCP\myTestSkipField.fmt 并执行以下修改：

复制 FirstName 的整个格式化文件行，并紧接在下一行的 FirstName 后面粘贴它。
对于新行和所有后续行，将主机文件字段顺序值增加一。
增加列数值以反映数据文件中的实际字段数。
对于第二个格式化文件行，将服务器列顺序从 2 修改为 0 。

比较进行的更改：

之前

输出

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

之后

输出

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

修改的格式化文件现在可反映：

4 个数据字段
myTestSkipField.bcp 中的第一个数据字段映射到第一列， myTestSkipField.. PersonID
myTestSkipField.bcp 中的第二个数据字段未映射到任何列。
myTestSkipField.bcp 中的第三个数据字段映射到第二列， myTestSkipField.. FirstName
myTestSkipField.bcp 中的第四个数据字段映射到第三列， myTestSkipField.. LastName

创建 XML 格式化文件

有关详细信息，请查看 XML 格式化文件 (SQL Server)。下面的命令使用 bcp 实用工具基于 myTestSkipField.xml的架构创建 xml 格式化文件 myTestSkipField。此外，限定符 c 用于指定字符数据， t, 用于将逗号指定为字段终止符，而 T 用于指定使用集成安全性的信任连接。 x 限定符必须用于生成基于 XML 的格式化文件。在命令提示符处输入以下命令：

Windows 命令提示符

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

修改 XML 格式化文件

有关术语，请查看 XML 格式化文件的架构语法。在记事本中打开 D:\BCP\myTestSkipField.xml 并执行以下修改：

复制整个第二个字段，并紧接在下一行的第二个字段后面粘贴它。
对于新 FIELD 和每个后续 FIELD，将“FIELD ID”值增加 1。
对于 FirstName和 LastName 将“COLUMN SOURCE”值增加 1，以反映修改的映射。

比较进行的更改：

之前

XML

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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>

之后

XML

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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>

修改的格式化文件现在可反映：

4 个数据字段
与 COLUMN 1 对应的 FIELD 1 映射到第一个表列， myTestSkipField.. PersonID
FIELD 2 不与任何 COLUMN 对应，因此不映射到任何表列。
与 COLUMN 3 对应的 FIELD 3 映射到第二个表列， myTestSkipField.. FirstName
与 COLUMN 4 对应的 FIELD 4 映射到第三个表列， myTestSkipField.. LastName

使用格式化文件导入数据以跳过数据字段

下面的示例使用上面创建的数据库、数据文件和格式化文件。

使用 bcp 和非 XML 格式化文件

在命令提示符处输入以下命令：

Windows 命令提示符

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

使用 bcp 和 XML 格式化文件

在命令提示符处输入以下命令：

Windows 命令提示符

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

使用 BULK INSERT 和非 XML 格式化文件

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

SQL

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 格式化文件

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

SQL

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 格式化文件

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

SQL

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 格式化文件

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

SQL

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;

后续步骤

其他资源

文档

使用格式化文件跳过表列 (SQL Server) - SQL Server

本文描述当源数据文件中不存在该已跳过列的数据时，如何使用格式化文件跳过导入表列。
使用格式化文件将表列映射到数据文件字段 - SQL Server

在 SQL Server 中，非 XML 格式化文件和 XML 格式化文件可以容纳字段排列顺序不同于表列顺序的数据文件。
使用 bcp 创建格式化文件 (SQL Server) - SQL Server

批量导入或导出 SQL Server 表时，格式化文件允许写入数据文件，很少需要从其他程序编辑或读取数据文件。
非 XML 格式化文件 (SQL Server) - SQL Server

SQL Server 2019 支持使用非 XML 格式化文件和 XML 格式化文件进行批量导入和导出。了解非 XML 格式化文件及其优点。
使用格式化文件批量导入数据 - SQL Server

在 SQL Server 中，可在批量导入操作中使用格式化文件。格式化文件可将数据文件的各字段映射到表的各列。
导入和导出数据的格式文件 - SQL Server

当批量导入到 SQL Server 表或从表中批量导出时，格式化文件可以存储相对于表的数据文件的字段格式信息。
XML 格式化文件 (SQL Server) - SQL Server

SQL Server 提供了一种 XML 架构，该架构定义了编写 XML 格式文件以将数据批量导入 SQL Server 表中的语法。
使用 bcp 指定文件存储类型 - SQL Server

使用 bcp 将数据作为其数据库表类型、以字符表示形式或作为支持隐式转换的数据类型导出到文件中。

培训

模块

浏览 Azure SQL 数据库中的数据操作选项 - Training

了解如何在 Azure SQL 数据库中调用 REST 终结点，并使用 Azure Functions 操作数据。此外，还可以浏览用于向/从 Azure SQL 数据库导入和导出数据的各种工具和选项。

活动

FabCon Vegas 的 SQL

3月31日 23时 - 4月2日 23时

最大的 SQL、Fabric 和 Power BI 学习事件。 3月31日至4月2日。使用代码 FABINSIDER 保存 $400。

立即注册

通过

使用格式化文件跳过数据字段 (SQL Server)

示例测试条件

示例表

示例数据文件

创建格式化文件

创建非 XML 格式化文件

修改非 XML 格式化文件

创建 XML 格式化文件

修改 XML 格式化文件

使用格式化文件导入数据以跳过数据字段

使用 bcp 和非 XML 格式化文件

使用 bcp 和 XML 格式化文件

使用 BULK INSERT 和非 XML 格式化文件

使用 BULK INSERT 和 XML 格式化文件

使用 OPENROWSET(BULK...) 和非 XML 格式化文件

使用 OPENROWSET(BULK...) 和 XML 格式化文件

后续步骤

反馈

其他资源