适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库 当对 SQL Server 表进行大容量的数据导入或导出时,您可使用格式化文件提供一个灵活的系统,用于写入需要少量编辑或不需要编辑即可符合其他数据格式的数据文件,或用于从其他软件程序读取数据文件。
SQL Server 支持两种类型的格式化文件:非 XML 格式和 XML 格式。 非 XML 格式是 SQL Server早期版本支持的原始格式。
通常,XML 与非 XML 格式化文件可以互换。 但是,建议您为新的格式化文件使用 XML 语法,因为与非 XML 格式化文件相比,格式化文件具有多项优点。
Note:
读取格式化文件所用的 bcp 实用工具 (Bcp.exe) 的版本必须与创建格式化文件所用的版本相同或更高。 例如, SQL Server 2012 (11.x)bcp 可以读取由 SQL Server 2008bcp生成的 10。0 版格式化文件,但 SQL Server 2008bcp 无法读取由 SQL Server 2012 (11.x)bcp。
本主题说明了如何使用 bcp 实用工具 为特定表创建格式化文件。 格式化文件基于指定的数据类型选项(-n、 -c、 -w或 -N)以及表或视图分隔符。
创建非 XML 格式化文件
若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。 format 选项还需要 -f 选项,例如:
bcp table_or_view format nul -fformat_file_name
Note:
为区分非 XML 格式化文件,我们建议使用 .fmt 作为文件扩展名,例如 MyTable.fmt。
有关非 XML 格式化文件结构和字段的信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。
示例
本部分包含以下示例,演示如何使用 bcp 命令创建非 XML 格式化文件:
-
A. 为本机数据创建非 XML 格式化文件
-
B. 为字符数据创建非 XML 格式化文件
-
C. 为 Unicode 本机数据创建非 XML 格式化文件
-
D. 为 Unicode 字符数据创建非 XML 格式化文件
-
F. 使用具有代码页选项的格式化文件
本示例使用 HumanResources.Department 示例数据库中的 AdventureWorks2012 表。 HumanResources.Department 表包含四列: DepartmentID、 Name、 GroupName和 ModifiedDate。
A. 为本机数据创建非 XML 格式化文件
以下示例将为 Department-n.xml表创建 XML 格式化文件 AdventureWorks2012HumanResources.Department 。 格式化文件使用本机数据类型。 在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
| 限定符 | 描述 |
|---|---|
| formatnul-f format_file | 指定非 XML 格式化文件。 |
| -n | 指定本机数据类型。 |
| -T | 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL Server 。 如果未指定 -T ,则必须指定 -U 和 -P 才能成功登录。 |
在 Windows 命令提示符下,输入以下 bcp 命令:
bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt
生成的格式化文件 Department-n.fmt包含以下信息:
12.0
4
1 SQLSMALLINT 0 2 "" 1 DepartmentID ""
2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 100 "" 3 GroupName SQL_Latin1_General_CP1_CI_AS
4 SQLDATETIME 0 8 "" 4 ModifiedDate ""
有关详细信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。
B. 为字符数据创建非 XML 格式化文件
以下示例将为 Department.fmt表创建 XML 格式化文件 AdventureWorks2012HumanResources.Department 。 格式化文件使用字符数据格式和非默认字段终止符 (,)。 在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
| 限定符 | 描述 |
|---|---|
| formatnul-f format_file | 指定非 XML 格式化文件。 |
| -c | 指定字符数据。 |
| -T | 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL Server 。 如果未指定 -T ,则必须指定 -U 和 -P 才能成功登录。 |
在 Windows 命令提示符下,输入以下 bcp 命令:
bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T
生成的格式化文件 Department-c.fmt包含以下信息:
12.0
4
1 SQLCHAR 0 7 "\t" 1 DepartmentID ""
2 SQLCHAR 0 100 "\t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "\t" 3 GroupName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 24 "\r\n" 4 ModifiedDate ""
有关详细信息,请参阅 非 XML 格式化文件 (SQL Server)早期版本支持的原始格式。
C. 为 Unicode 本机数据创建非 XML 格式化文件
若要为 HumanResources.Department 表中的 Unicode 本机数据创建非 XML 格式化文件,请使用以下命令:
bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt
有关如何使用 Unicode 本机数据的详细信息,请参阅使用 Unicode 本机格式导入或导出数据 (SQL Server)。
D. 为 Unicode 字符数据创建非 XML 格式化文件
若要为 HumanResources.Department 表中使用默认终止符的 Unicode 字符数据创建非 XML 格式化文件,请使用以下命令:
bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt
有关如何使用 Unicode 字符数据的详细信息,请参阅使用 Unicode 字符格式导入或导出数据 (SQL Server)。
F. 使用具有代码页选项的格式化文件
如果使用 bcp 命令(即使用 bcp format)创建格式化文件,排序规则/代码页的相关信息将写入格式化文件。
具有 5 列的表的以下示例格式化文件包括了排序规则。
13.0
5
1 SQLCHAR 0 0 "**\t**" 1 c_0 Cyrillic_General_CS_AS
2 SQLCHAR 0 0 "**\t**" 2 c_1 Cyrillic_General_CS_AS
3 SQLCHAR 0 3000 "**\t**" 3 c_2 Cyrillic_General_CS_AS
4 SQLCHAR 0 5 "**\t**" 4 c_3 ""
5 SQLCHAR 0 41 "!!!\r\r\n" 5 c_4 ""
如果尝试使用“ SQL Server …”将数据导入到 bcp in –c –C65001 –f format_file , 或“BULK INSERT/OPENROWSET … FORMATFILE='format_file' CODEPAGE=65001 …”),有关排序规则/代码页的信息会优先于 65001 选项。
因此,如要生成格式化文件,必须在开始将数据导回 SQL Server之前,手动从生成的格式化文件中删除排序规则信息。
以下是不具有排序规则信息的格式化文件的示例。
13.0
5
1 SQLCHAR 0 0 "**\t**" 1 c_0 ""
2 SQLCHAR 0 0 "**\t**" 2 c_1 ""
3 SQLCHAR 0 3000 "**\t**" 3 c_2 ""
4 SQLCHAR 0 5 "**\t**" 4 c_3 ""
5 SQLCHAR 0 41 "!!!\r\r\n" 5 c_4 ""
创建 XML 格式化文件
若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。 format 选项始终要求 -f 选项,若要创建 XML 格式化文件,还必须指定 -x 选项,例如:
bcp table_or_view format nul-f format_file_name -x
Note:
为区分 XML 格式化文件,我们建议使用 .xml 作为文件扩展名,例如 MyTable.xml。
有关 XML 格式化文件结构和字段的信息,请参阅 XML 格式化文件 (SQL Server)早期版本支持的原始格式。
示例
本部分包含以下示例,演示如何使用 bcp 命令创建 XML 格式化文件:
-
A. 为字符数据创建 XML 格式化文件
-
B. 为本机数据创建 XML 格式化文件
本示例使用 AdventureWorks2012 示例数据库中的 HumanResources.Department 表。 HumanResources.Department 表包含四列: DepartmentID、 Name、 GroupName和 ModifiedDate。
Note:
Adventure Works Cycles 是一家虚构的制造公司,用于演示数据库概念和方案。
A. 为字符数据创建 XML 格式化文件
以下示例将为 Department.xml表创建 XML 格式化文件 AdventureWorks2012HumanResources.Department 。 格式化文件使用字符数据格式和非默认字段终止符 (,)。 在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
| 限定符 | 描述 |
|---|---|
| formatnul-f format_file -x | 指定 XML 格式化文件。 |
| -c | 指定字符数据。 |
-t , |
将逗号 (,) 指定为字段终止符。 请注意:如果数据文件使用默认的字段终止符 (\t),则不需要 -t 开关。 |
| -T | 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL Server 。 如果未指定 -T ,则必须指定 -U 和 -P 才能成功登录。 |
在 Windows 命令提示符下,输入以下 bcp 命令:
bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c..xml –t, -T
生成的格式化文件 Department-c.xml包含以下 XML 元素:
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="https://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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>
有关此格式化文件语法的详细信息,请参阅 XML 格式化文件 (SQL Server)。 有关字符数据的详细信息,请参阅使用字符格式导入或导出数据 (SQL Server)。
B. 为本机数据创建 XML 格式化文件
以下示例将为 Department-n.xml表创建 XML 格式化文件 HumanResources.Department 。 格式化文件使用本机数据类型。 在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
| 限定符 | 描述 |
|---|---|
| formatnul-f format_file -x | 指定 XML 格式化文件。 |
| -n | 指定本机数据类型。 |
| -T | 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL Server 。 如果未指定 -T ,则必须指定 -U 和 -P 才能成功登录。 |
在 Windows 命令提示符下,输入以下 bcp 命令:
bcp AdventureWorks2012.HumanResources.Department format nul -x -f Department-n..xml -n -T
生成的格式化文件 Department-n.xml包含以下 XML 元素:
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="2"/>
<FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>
有关此格式化文件语法的详细信息,请参阅 XML 格式化文件 (SQL Server)。 有关如何使用本机数据的详细信息,请参阅使用本机格式导入或导出数据 (SQL Server)。
将数据字段映射到表列
如同使用 bcp创建一样,格式化文件按顺序说明所有的表列。 可以修改格式化文件以重新安排或忽略表列。 这便于您针对字段未直接映射到表列的数据文件来自定义格式化文件。 有关详细信息,请参阅以下主题:
另请参阅
bcp 实用工具
使用格式化文件将表列映射到数据文件字段 (SQL Server)
使用格式化文件跳过表列 (SQL Server)
使用格式化文件跳过数据字段 (SQL Server)
非 XML 格式化文件 (SQL Server)
XML 格式化文件 (SQL Server)
历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。
来源与署名:Microsoft 和文档贡献者(原始作者元数据 douglaslMS)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。
