The bcp utility can create or read data files in four default data formats by specifying a parameter at the command prompt.
| Data format | bcp utility parameter | 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™ for further information needed to specify the data format. However, when using the -n, -c, -w, or -N parameters, 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 is not applicable.
Additionally, the -6 parameter causes the bcp utility to modify native (-n) or character (-c) data to a format compatible with SQL Server version 6.0 or 6.5 clients.
The recommended default data format used depends on the type of bulk copy operation that needs to be performed.
Bulk copy operation |
Native |
Character |
Unicode character |
Unicode native |
|
|---|---|---|---|---|---|
| Bulk copying data from SQL Server to SQL Server using a data file (no extended/DBCS characters involved). | YES (1) | ||||
| Bulk copying data from SQL Server to 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 from SQL Server to 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. | |||||
Note The -6 parameter is not applicable to the BULK INSERT statement.