Bulk Import or export data format--Unicode character format

Bulk Import or export data format--Unicode Character Format

Application Scenarios

When using data files that contain extended/dbcs characters to bulk transfer data between multiple instances of SQL Server , it is recommended that you use Unicode character formatting.

When you export data from a server, the Unicode character data format allows you to use a different code page than the client that performs the operation .

In this case, the use of Unicode character formats has the following advantages:

1. If the source and destination data are of type Unicode, all character data can be preserved using Unicode character format.

2. If the source and destination data types are not Unicode, use the Unicode character format to minimize the loss of extended characters in the source data that cannot be represented in the target data.

Unicode character format data files follow the conventions of Unicode files. The first two bytes of the file are hexadecimal digits 0xFFFE. These two bytes are used as byte order marks, specifying whether the high-level bytes in the file are stored in front or behind.

Important Notes

For a format file for Unicode character data files, all input fields must be Unicode text strings (that is, fixed-size Unicode strings or characters terminating Unicode strings).

The sql_variant data stored in a Unicode character format data file operates in the same way as the same data in a character format data file, except that the data is stored as nchar instead of char data.

Unicode command options for character formatting

You can use bcp, BULK Insert, or INSERT ... SELECT * from OPENROWSET (BULK ...) to import character format data into a table. For bcp commands or BULK INSERT statements, you can specify the data format on the command line. For INSERT ... SELECT * from OPENROWSET (BULK ...) statement, you must specify the data format in the format file.

The following command-line options support Unicode character formatting:

Command	Options	Description
Bcp	-W	Use Unicode character formatting.
BULK INSERT	DataFileType = 'widechar'	Unicode character formatting is used when bulk importing data.

Reference:

Https://msdn.microsoft.com/zh-cn/library/ms188289.aspx

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1628101

Bulk Import or export data format--Unicode character format