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:
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.