Unicode Character Format
The -w switch (or widechar value for the DATAFILETYPE clause of the BULK INSERT statement) uses the Unicode character data format for all columns, providing, as default terminators, tabs between fields and a newline character at the end of each row. This allows data to be copied both from a server using a code page different from the code page used by the client running bcp, and to another server with the same (or a different) code page as the original server:
- Without loss of any character data, if the source and destination are Unicode data types.
- With minimal loss of extended characters in the source data that cannot be represented at the destination if the source and destination are not Unicode data types.
For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt file using Unicode character format is:
bcp pubs..publishers out publ.txt -w -Sservername -Usa -Ppassword
Unicode character format data files follow the conventions for Unicode files: the first two bytes of the file are either of the hexadecimal numbers 0xFEFF or 0xFFFE. These bytes serve as byte-order marks, specifying whether the high-order byte is stored first or last in the file.
To use field and row terminators other than the default provided with Unicode character format, specify the following.
| Terminator | bcp utility switch | BULK INSERT clause |
|---|---|---|
| Field | -t | FIELDTERMINATOR |
| Row | -r | ROWTERMINATOR |
For example, the command to bulk copy the publishers table to the Publ.txt data file using Unicode character format, with a comma as a field terminator and the newline character (\n) as the row terminator, is:
bcp pubs..publishers out publ.txt -w -t , -r \n -Sservername -Usa -Ppassword
Two character positions are used for each character in the Publ.txt data file, with each field separated by a comma, and each row separated by a newline character.
sql_variant data stored in a Unicode character mode data file operates the same way it does in a character mode data file, except that the data is stored as nchar instead of char data.