Using Unicode Native Format to Import or Export Data

Unicode native format is helpful when information must be copied from one Microsoft SQL Server installation to another. The use of native format for noncharacter data saves time, eliminating unnecessary conversion of data types to and from character format. The use of Unicode character format for all character data prevents loss of any extended characters during bulk transfer of data between servers using different code pages. A data file in Unicode native format can be read by any bulk-import method.

Unicode native format is recommended for the bulk transfer of data between multiple instances of SQL Server by using a data file that contains extended or DBCS characters. For noncharacter data, Unicode native format uses native (database) data types. For character data, such as char, nchar, varchar, nvarchar, text, varchar(max), nvarchar(max), and ntext, the Unicode native format uses Unicode character data format.

The sql_variant data that is stored as a SQLVARIANT in a Unicode native-format data file operates in the same manner as it does in a native-format data file, except that char and varchar values are converted to nchar and nvarchar, which doubles the amount of storage required for the affected columns. The original metadata is preserved, and the values are converted back to their original char and varchar data type when bulk imported into a table column.

Command Options for Unicode Native Format

You can import Unicode native 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 native format is supported by the following options:

For more information, see bcp Utility, BULK INSERT (Transact-SQL), or OPENROWSET (Transact-SQL).

Examples

The following examples demonstrate how to bulk export native data using bcp and bulk import the same data using BULK INSERT.

Sample Table

The examples require that a table named myTestUniNativeData table be created in the AdventureWorks sample database under the dbo schema. Before you can run the examples, you must create this table. In SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
CREATE TABLE myTestUniNativeData (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50)
   ); 

To populate this table and view the resulting contents execute the following statements:

INSERT INTO myTestUniNativeData(Col1,Col2,Col3)
   VALUES(1,'DataField2','DataField3');
INSERT INTO myTestUniNativeData(Col1,Col2,Col3)
   VALUES(2,'DataField2','DataField3');
GO
SELECT Col1,Col2,Col3 FROM myTestUniNativeData

Using bcp to Bulk Export Native Data

To export data from the table to the data file, use bcp with the out option and the following qualifiers:

The following example bulk exports data in native format from the myTestUniNativeData table into a new data file named myTestUniNativeData-N.Dat data file. At the Microsoft Windows command prompt, enter:

bcp AdventureWorks..myTestUniNativeData out C:\myTestUniNativeData-N.Dat -N -T

Using BULK INSERT to Bulk Import Native Data

The following example uses BULK INSERT to import the data in the myTestUniNativeData-N.Dat data file into the myTestUniNativeData table. In SQL Server Management Studio Query Editor, execute:

USE AdventureWorks;
GO
BULK INSERT myTestUniNativeData 
    FROM 'C:\myTestUniNativeData-N.Dat' 
   WITH (DATAFILETYPE='widenative'); 
GO
SELECT Col1,Col2,Col3 FROM myTestUniNativeData;
GO

See Also

Concepts

Working with Unicode Data

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Data Types (Transact-SQL)
International Considerations for Databases and Database Engine Applications
ServerBCPDataFileType Property

Help and Information

Getting SQL Server 2005 Assistance