使用格式化文件将表列映射到数据文件字段 (SQL Server)

项目
2025/01/02

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

数据文件中包含的字段的排列顺序可能不同于表中相应列的顺序。本文介绍修改为容纳其字段顺序与表列不同的数据文件的非 XML 和 XML 格式化文件。修改后的格式化文件可将数据字段映射到与之相应的表列。有关其他信息，请查看使用 bcp （SQL Server）创建格式化文件。

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

备注

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

示例测试条件

本文中修改的格式文件的示例基于本文稍后定义的表和数据文件。

示例表

以下 Transact-SQL 脚本创建一个名为的测试数据库和一个表 myRemap。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

示例数据文件

以下数据按FirstNameLastName表myRemap所示按相反顺序显示。使用文本编辑器（如记事本）创建一个空文件 D:\BCP\myRemap.bcp 并插入以下数据：

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

创建格式化文件

若要从myRemap.bcpmyRemap表中批量导入数据，格式化文件必须执行以下操作：

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

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

创建非 XML 格式化文件

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

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

修改非 XML 格式化文件

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

重新排列格式化文件行的顺序，使行的顺序与数据 myRemap.bcp的顺序相同。
确保主机文件字段顺序值是顺序的。
确保最后一个格式化文件行之后有回车符。

比较更改：

之前

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

之后

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

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

myRemap.bcp 中的第一个数据字段映射到第一列， myRemap.. PersonID
myRemap.bcp 中的第二个数据字段映射到第三列， myRemap.. LastName
myRemap.bcp 中的第三个数据字段映射到第二列， myRemap.. FirstName
myRemap.bcp 中的第四个数据字段映射到第四列， myRemap.. Gender

创建 XML 格式化文件

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

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

修改 XML 格式化文件

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

元素在格式化文件中声明的顺序 <FIELD> 是这些字段出现在数据文件中的顺序。因此，将 ID 属性为 2 和 3 的 <FIELD> 元素反转顺序。
确保 <FIELD> ID 属性值是顺序的。
ROW> 元素中<元素的顺序<COLUMN>定义批量操作将其发送到目标的顺序。 XML 格式化文件为每个 <COLUMN> 元素分配了一个本地名称，该名称与批量导入操作的目标表中的列没有关系。元素的顺序<COLUMN>与 RECORD> 定义中的<元素顺序<FIELD>无关。每个 <COLUMN> 元素对应一个 <FIELD> 元素（其 ID 在 <COLUMN> 元素的 SOURCE 属性中指定）。因此，<COLUMN> SOURCE 的值是需要修订的唯一属性。颠倒 <COLUMN> SOURCE 属性 2 和 3 的顺序。

比较更改：

之前

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

之后

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

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

与 COLUMN 1 对应的 FIELD 1 映射到第一个表列， myRemap.. PersonID
与 COLUMN 2 对应的 FIELD 2 重新映射到第三个表列， myRemap.. LastName
与 COLUMN 3 对应的 FIELD 3 重新映射到第二个表列， myRemap.. FirstName
与 COLUMN 4 对应的 FIELD 4 映射到第四个表列， myRemap.. Gender

使用格式化文件导入数据以将表列映射到数据文件字段

以下示例使用之前创建的数据库、数据文件和格式化文件。

使用 bcp 和非 XML 格式化文件

在命令提示符处，输入以下命令。

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

使用 bcp 和 XML 格式化文件

在命令提示符处，输入以下命令。

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

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

运行以下 Transact-SQL 脚本。

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

运行以下 Transact-SQL 脚本。

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

运行以下 Transact-SQL 脚本。

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

运行以下 Transact-SQL 脚本。

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;

通过

使用格式化文件将表列映射到数据文件字段 (SQL Server)

示例测试条件

示例表

示例数据文件

创建格式化文件

创建非 XML 格式化文件

修改非 XML 格式化文件

创建 XML 格式化文件

修改 XML 格式化文件

使用格式化文件导入数据以将表列映射到数据文件字段

使用 bcp 和非 XML 格式化文件

使用 bcp 和 XML 格式化文件

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

使用 BULK INSERT 和 XML 格式化文件

使用 OPENROWSET 和非 XML 格式化文件

使用 OPENROWSET 和 XML 格式化文件

反馈

其他资源

通过

使用格式化文件将表列映射到数据文件字段 (SQL Server)

示例测试条件

示例表

示例数据文件

创建格式化文件

创建非 XML 格式化文件

修改非 XML 格式化文件

创建 XML 格式化文件

修改 XML 格式化文件

使用格式化文件导入数据以将表列映射到数据文件字段

使用 bcp 和非 XML 格式化文件

使用 bcp 和 XML 格式化文件

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

使用 BULK INSERT 和 XML 格式化文件

使用 OPENROWSET 和非 XML 格式化文件

使用 OPENROWSET 和 XML 格式化文件

相关内容

反馈

其他资源