Bewerken

Delen via

Use Unicode Native Format to Import or Export Data (SQL Server)

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

In this Topic:
Command Options for Unicode Native Format
Example Test Conditions
 ● Sample Table
 ● Sample Non-XML Format File
Examples
 ● Using bcp and Unicode Native Format to Export Data
 ● Using bcp and Unicode Native Format to Import Data without a Format File
 ● Using bcp and Unicode Native Format to Import Data with a Non-XML Format File
 ● Using BULK INSERT and Unicode Native Format without a Format File
 ● Using BULK INSERT and Unicode Native Format with a Non-XML Format File
 ● Using OPENROWSET and Unicode Native Format with a Non-XML Format File
Related Tasks

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 in the statement. 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 command options:

Command Option Description
bcp -N Causes the bcp utility to use the Unicode native format, which uses native (database) data types for all noncharacter data and Unicode character data format for all character (char, nchar, varchar, nvarchar, text, and ntext) data.
BULK INSERT DATAFILETYPE ='widenative' Uses Unicode native format when bulk importing data.
OPENROWSET N/A Must use a format file

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 (SQL Server).

Example Test Conditions

The examples in this topic are based on the table, and format file defined below.

Sample Table

The script below creates a test database, a table named myWidenative and populates the table with some initial values. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

-- Populate table
INSERT TestDatabase.dbo.myWidenative
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.myWidenative;

Sample Non-XML Format File

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myWidenative.fmt, based on the schema of myWidenative. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following commands:

bcp TestDatabase.dbo.myWidenative format nul -f D:\BCP\myWidenative.fmt -T -N

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

Important

Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:

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

Examples

The examples below use the database, and format files created above.

Using bcp and Unicode Native Format to Export Data

-N switch and OUT command. Note: the data file created in this example will be used in all subsequent examples. At a command prompt, enter the following commands:

bcp TestDatabase.dbo.myWidenative OUT D:\BCP\myWidenative.bcp -T -N

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

Using bcp and Unicode Native Format to Import Data without a Format File

-N switch and IN command. At a command prompt, enter the following commands:

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

REM Import data
bcp TestDatabase.dbo.myWidenative IN D:\BCP\myWidenative.bcp -T -N

REM Review results is SSMS

Using bcp and Unicode Native Format to Import Data with a Non-XML Format File

-N and -f switches and IN command. At a command prompt, enter the following commands:

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

REM Import data
bcp TestDatabase.dbo.myWidenative IN D:\BCP\myWidenative.bcp -f D:\BCP\myWidenative.fmt -T -N

REM Review results is SSMS

Using BULK INSERT and Unicode Native Format without a Format File

DATAFILETYPE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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

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

Using BULK INSERT and Unicode Native Format with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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

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

Using OPENROWSET and Unicode Native Format with a Non-XML Format File

FORMATFILE argument. Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

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

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

To use data formats for bulk import or bulk export

See Also

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Data Types (Transact-SQL)