Bulk Import or export data format-native format

Bulk Import or export of data formats-- native Format

Application Scenarios

When using data files that do not contain any extended/double-byte character set (DBCS) characters to bulk transfer data between multiple instances of SQL Server , it is recommended that you use a native format.

The native format retains the native data type of the database .

The native format applies to high-speed data transfer between SQL Server tables.

If you use a format file, the source and destination tables do not have to be the same.

The data transfer is divided into two steps:

1. Bulk export of data from the source table into a data file

2. Bulk import of data from data files into the target table

Using native formats between the same tables avoids unnecessary conversions between data types and character formats, saving time and space.

However, to get the best transfer rate, you should perform several checks on the data format.

To prevent problems with the loaded data, see the following list of restrictions.

Limit

To import data from a native format successfully, make sure that:

1. The data file is a native format file.

2. The target table must be compatible with the data file (containing the correct number of columns, data type, length, and NULL state, etc.), or you must use a format file to map each field to its corresponding column.

Attention

If you import data from a file that does not match the target table, the import operation may succeed, but the data values inserted into the target table are likely to be incorrect. This is because the data in the file is interpreted by using the format of the destination table. Therefore, any mismatch will result in the insertion of an error value. However, such mismatches never result in logical or physical inconsistencies in the database.

A successful import operation does not damage the target table.

bcp How to handle data in native format

This section describes special considerations for how the bcp utility exports and imports native format data.

1. Non-character data

The bcp utility uses the SQL Server internal binary data format to write non-character data from a table into a data file.

2. Char data or varchar data

At the beginning of each char field or varchar field, bcp adds a prefix length.

Important Notes

When native mode is used, by default, thebcp utility converts characters in SQL Server to OEM characters, and then copies the characters into a data file. The bcp utility first converts the characters in the data file to ANSI characters, and then bulk imports those characters into the SQL Server table. During these conversions, extended character data may be lost. For extended characters, use Unicode native format or specify a code page.

3. Sql_variant data

If the sql_variant data is stored in sqlvariant in a native format data file, the data retains all its characteristics. Metadata that records the data type of each data value is stored with the data value. This metadata is used to recreate data values in the target sql_variant column that have the same data type.

If the data type of the target column is not sql_variant, then each data value is converted to the data type of the destination column according to the general rule of implicit data conversion. If an error occurs during the data conversion process, the current batch is rolled back. Any char and varchar values that are transferred between the sql_variant columns may have a code page conversion problem.

command options in native format

You can use bcp, BULK Insert, or INSERT ... SELECT * from OPENROWSET (BULK ...) imports native format data into the 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 the native format:

Command	Options	Description
Bcp	-N	Enables the bcp utility to use data of the native data type. 1
BULK INSERT	DataFileType = 'native'	Use data of a native data type or a wide native data type. Note that if the format file specifies a data type, DataFileType is not required.

To load native (-N) data into a format that is compatible with earlier versions of SQL Server client, use the-v switch.

Reference:

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

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

Bulk Import or export data format-native format