Using Unicode Character Format to Import or Export Data
Unicode character format is recommended for bulk transfer of data between multiple instances of Microsoft SQL Server by using a data file that contains extended/DBCS characters. The Unicode character data format allows data to be exported from a server by using a code page that differs from the code page used by the client that is performing the operation. In such cases, use of Unicode character format has the following advantages:
If the source and destination data are Unicode data types, use of Unicode character format preserves all of the character data.
if the source and destination data are not Unicode data types, use of Unicode character format minimizes the loss of extended characters in the source data that cannot be represented at the destination.
Unicode character format data files follow the conventions for Unicode files. The first two bytes of the file are hexadecimal numbers, 0xFFFE. These bytes serve as byte-order marks, specifying whether the high-order byte is stored first or last in the file.
Important
For a format file to work with a Unicode character data file, all the input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
The sql_variant data that is stored in a Unicode character-format data file operates in the same way it operates in a character-format data file, except that the data is stored as nchar instead of char data. For more information about character format, see Using Character Format to Import or Export Data.
To use a field or row terminator other than the default that is provided with Unicode character format, see Specifying Field and Row Terminators.
Command Options for Unicode Character Format
You can import Unicode character format data into a table using bcp, BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...). For a bcp command or BULK INSERT statement, you can specify the data format on the command line. For an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, you must specify the data format in a format file.
Unicode character format is supported by the following command line options:
Command |
Option |
Description |
|---|---|---|
bcp |
-w |
Uses the Unicode character format. |
BULK INSERT |
DATAFILETYPE ='widechar' |
Uses Unicode character format when bulk importing data. |
For more information, see bcp Utility, BULK INSERT (Transact-SQL), or OPENROWSET (Transact-SQL).
Note
Alternatively, you can specify formatting on a per-field basis in a format file. For more information, see Format Files for Importing or Exporting Data.
Examples
The following examples demonstrate how to bulk export Unicode character data using bcp and to bulk import the same data using BULK INSERT.
Sample Table
The examples require that a table named myTestUniCharData table be created in the AdventureWorks2008R2 sample database under the dbo schema. Before you can run the examples, you must create this table. To create this table, in SQL Server Management Studio Query Editor, execute:
USE AdventureWorks2008R2;
GO
CREATE TABLE myTestUniCharData (
Col1 smallint,
Col2 nvarchar(50),
Col3 nvarchar(50)
);
To populate this table and view the resulting contents execute the following statements:
INSERT INTO myTestUniCharData(Col1,Col2,Col3)
VALUES(1,'DataField2','DataField3');
INSERT INTO myTestUniCharData(Col1,Col2,Col3)
VALUES(2,'DataField2','DataField3');
GO
SELECT Col1,Col2,Col3 FROM myTestUniCharData
Using bcp to Bulk Export Unicode Character Data
To export data from the table to the data file, use bcp with the out option and the following qualifiers:
Qualifiers |
Description |
|---|---|
-w |
Specifies Unicode character format. |
-t, |
Specifies a comma (,) as the field terminator. .gif "Note") Note
The default field terminator is the tab Unicode character (\t). For more information, see Specifying Field and Row Terminators.
|
-T |
Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T is not specified, you need to specify -U and -P to successfully log in. |
The following example bulk exports data in Unicode character format from the myTestUniCharData table into a new data file named myTestUniCharData-w.Dat data file that uses the comma (,) as the field terminator. At the Microsoft Windows command prompt, enter:
bcp AdventureWorks2008R2..myTestUniCharData out C:\myTestUniCharData-w.Dat -w -t, -T
Using BULK INSERT to Bulk Import Unicode Character Data
The following example uses BULK INSERT to import the data in the myTestUniCharData-w.Dat data file into the myTestUniCharData table. The nondefault field terminator (,) must be declared in the statement. In SQL Server Management Studio Query Editor, execute:
USE AdventureWorks2008R2;
GO
BULK INSERT myTestUniCharData
FROM 'C:\myTestUniCharData-w.Dat'
WITH (
DATAFILETYPE='widechar',
FIELDTERMINATOR=','
);
GO
SELECT Col1,Col2,Col3 FROM myTestUniCharData;
GO