使用 Unicode 字符格式导入或导出数据 (SQL Server)

  • 项目

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

使用包含扩展/DBCS 字符的数据文件在多个 SQL Server 实例之间大容量传输数据时，建议使用 Unicode 字符格式。 从服务器导出数据时，Unicode 字符数据格式允许使用与执行该操作的客户端不同的代码页。 在这种情况下，使用 Unicode 字符格式有下列优点：

  • 如果源数据和目标数据的类型为 Unicode，则使用 Unicode 字符格式可以保留所有的字符数据。

  • 如果源数据和目标数据不为 Unicode 数据类型，则使用 Unicode 字符格式可以尽可能减少丢失源数据中无法在目标数据中表示的扩展字符。

本主题内容：
使用 Unicode 字符格式的注意事项
使用 Unicode 字符格式、bcp 和格式化文件的特殊注意事项
Unicode 字符格式的命令选项
示例测试条件
 ● 示例表
 ● 非 XML 格式化文件示例
示例
 ● 使用 bcp 和 Unicode 字符格式导出数据
 ● 在不使用格式化文件的情况下使用 bcp 和 Unicode 字符格式导入数据
 ● 在使用非 XML 格式化文件的情况下使用 bcp 和 Unicode 字符格式导入数据
 ● 在不使用格式化文件的情况下使用 BULK INSERT 和 Unicode 字符格式
 ● 在使用非 XML 格式化文件的情况下使用 BULK INSERT 和 Unicode 字符格式
 ● 在使用非 XML 格式化文件的情况下使用 OPENROWSET 和 Unicode 字符格式
相关任务

使用 Unicode 字符格式的注意事项

使用 Unicode 字符格式时，请考虑下列事项：

使用 Unicode 字符格式、bcp 和格式化文件的特殊注意事项

Unicode 字符格式数据文件遵循 Unicode 文件的约定。 该文件的前两个字节为十六进制数字 0xFFFE。 这两个字节用作字节顺序标记 (BOM)，指定在文件中高位字节是存储在前面还是后面。 bcp 实用工具 可能曲解字节顺序标记，并导致部分导入过程失败；可能会收到如下错误消息：

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification

在以下情况下，字节顺序标记可能被曲解：

  • 使用 bcp 实用工具 和 -w 切换表示 Unicode 字符

  • 使用格式化文件

  • 数据文件中的第一个字段是非字符

考虑以下任意解决方法是否适用于特定 情况：

Unicode 字符格式的命令选项

可以使用 bcp、BULK INSERT 或 INSERT ... SELECT * FROM OPENROWSET(BULK...) 将 Unicode 字符格式数据导入表中。对于 bcp 命令或 BULK INSERT 语句，你可以在语句中指定数据格式。 对于 INSERT ...SELECT * FROM OPENROWSET(BULK...) 语句，必须在格式化文件中指定数据格式。

下列命令选项支持 Unicode 字符格式：

Command 选项 说明
bcp -w 使用 Unicode 字符格式。
BULK INSERT DATAFILETYPE ='widechar' 批量导入数据时使用 Unicode 字符格式。
OPENROWSET 空值 必须使用格式化文件

注意

或者，您可以在格式化文件中为每个字段指定格式设置。 有关详细信息，请参阅用来导入或导出数据的格式化文件 (SQL Server)。

示例测试条件

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

示例表

以下脚本将创建测试数据库、名为 myWidechar 的表并用一些初始值填充表。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myWidechar ( 
	PersonID smallint NOT NULL,
	FirstName nvarchar(25) NOT NULL,
	LastName nvarchar(30) NOT NULL,
	BirthDate date,
	AnnualSalary money
);

-- Populate table
INSERT TestDatabase.dbo.myWidechar
VALUES 
(1, N'ϴAnthony', N'Grosse', '02-23-1980', 65000.00),
(2, N'❤Alica', N'Fatnowna', '11-14-1963', 45000.00),
(3, N'☎Stella', N'Rossenhain', '03-02-1992', 120000.00);

-- Review Data
SELECT * FROM TestDatabase.dbo.myWidechar;

示例非 XML 格式化文件

SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。 非 XML 格式是 SQL Server 早期版本支持的原始格式。 有关详细信息，请查看 非 XML 格式化文件 (SQL Server) 。 下面的命令基于 的架构使用 bcp 实用工具 myWidechar.fmt生成非 XML 格式化文件 myWidechar。 若要使用 bcp 命令创建格式化文件，请指定 format 参数，并使用 nul 而不是数据文件路径。 格式化选项还需要 -f 选项。 此外，对于本示例，限定符 c 用于指定字符数据， T 用于指定使用集成安全性的受信任连接。 在命令提示符处输入以下命令：

bcp TestDatabase.dbo.myWidechar format nul -f D:\BCP\myWidechar.fmt -T -w

REM Review file
Notepad D:\BCP\myWidechar.fmt

重要

确保非 XML 格式化文件以回车符/换行符结尾。 否则可能会收到以下错误消息：

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

示例

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

使用 bcp 和 Unicode 字符格式导出数据

-w 切换和 OUT 命令。 请注意：此示例中创建的数据文件将用于所有后续示例中。 在命令提示符处输入以下命令：

bcp TestDatabase.dbo.myWidechar OUT D:\BCP\myWidechar.bcp -T -w

REM Review results
NOTEPAD D:\BCP\myWidechar.bcp

在不使用格式化文件的情况下使用 bcp 和 Unicode 字符格式导入数据

-w 切换和 IN 命令。 在命令提示符处输入以下命令：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myWidechar;"

REM Import data
bcp TestDatabase.dbo.myWidechar IN D:\BCP\myWidechar.bcp -T -w

REM Review results is SSMS

在使用非 XML 格式化文件的情况下使用 bcp 和 Unicode 字符格式导入数据

-w 和 -f 切换以及 IN 命令。 由于此示例讲到 bcp、格式化文件、Unicode 字符以及数据文件中的第一个数据字段为非字符，因此需要使用解决方法。 请参阅上面的 使用 Unicode 字符格式、bcp 和格式化文件的特殊注意事项。 数据文件 myWidechar.bcp 将通过添加其他记录为“虚拟”记录来进行更改，然后使用 -F 2 开关跳过该记录。

在命令提示符中，输入以下命令，然后按照修改步骤操作：

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myWidechar;"

REM Open data file
Notepad D:\BCP\myWidechar.bcp
REM Copy first record and then paste as new first record.  This additional record is the "dummy" record.
REM Close file.

REM Import data instructing bcp to skip dummy record with the -F 2 switch.
bcp TestDatabase.dbo.myWidechar IN D:\BCP\myWidechar.bcp -f D:\BCP\myWidechar.fmt -T -F 2

REM Review results is SSMS

REM Return data file to original state for usage in other examples
bcp TestDatabase.dbo.myWidechar OUT D:\BCP\myWidechar.bcp -T -w

在不使用格式化文件的情况下使用 BULK INSERT 和 Unicode 字符格式

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

TRUNCATE TABLE TestDatabase.dbo.myWidechar; -- for testing
BULK INSERT TestDatabase.dbo.myWidechar
	FROM 'D:\BCP\myWidechar.bcp'
	WITH (
		DATAFILETYPE = 'widechar'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myWidechar;

在使用非 XML 格式化文件的情况下使用 BULK INSERT 和 Unicode 字符格式

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

TRUNCATE TABLE TestDatabase.dbo.myWidechar; -- for testing
BULK INSERT TestDatabase.dbo.myWidechar
   FROM 'D:\BCP\myWidechar.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myWidechar.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myWidechar;

在使用非 XML 格式化文件的情况下使用 OPENROWSET 和 Unicode 字符格式

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

TRUNCATE TABLE TestDatabase.dbo.myWidechar;  -- for testing
INSERT INTO TestDatabase.dbo.myWidechar
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myWidechar.bcp', 
		FORMATFILE = 'D:\BCP\myWidechar.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myWidechar;

使用数据格式进行批量导入或批量导出

另请参阅

bcp 实用工具
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
数据类型 (Transact-SQL)
排序规则和 Unicode 支持