Using Native, Character, and Unicode Formats
The bcp utility can create or read data files in the following default data formats by specifying a switch at the command prompt.
| Data format | bcp utility switch | BULK INSERT clause |
|---|---|---|
| Native | -n | DATAFILETYPE = 'native' |
| Character | -c | DATAFILETYPE = 'char' |
| Unicode character | -w | DATAFILETYPE = 'widechar' |
| Unicode native | -N | DATAFILETYPE = 'widenative' |
By default, the bcp utility operates in interactive mode and queries Microsoft® SQL Server™ and the user for information required to specify the data format. However, when using the -n, -c, -w, or -N switches, bcp does not query for information about the SQL Server table on a column-by-column basis. It reads or writes the data using the default format specified.
By default, the BULK INSERT statement operates in character mode (char). Interactive mode does not apply.
Additionally, the –V switch causes the bcp utility to modify native (-n) or character (-c) data to a format compatible with earlier versions of SQL Server clients. For more information, see Copying Native and Character Format Data from Earlier Versions of SQL Server.
Native mode bulk copies are best for sql_variant columns. Unlike character or Unicode bulk copies, native mode bulk copies preserve the meta data for each sql_variant value.
The recommended default data format depends on the type of bulk copy operation.
Bulk copy operation |
Native |
Character |
Unicode character | Unicode native |
|---|---|---|---|---|
| Bulk copying data between multiple instances of SQL Server using a data file (no extended/double-byte character set (DBCS) characters involved). | YES 1 | -- | -- | -- |
| Bulk copying data between multiple instances of SQL Server using a data file (extended/DBCS characters involved). | -- | -- | -- | YES |
| Exporting data to a text file to be used in another program. | -- | YES | -- | -- |
| Importing data from a text file generated by another program. | -- | YES | -- | -- |
| Bulk copying data between multiple instances of SQL Server using a data file (Unicode data/no extended/DBCS characters). | -- | -- | YES | -- |
1 Fastest method for bulk copying data from SQL Server using bcp.