Unicode Character Format bcp

The -w parameter (or widechar value for the DATAFILETYPE clause of the BULK INSERT statement) uses the Unicode character data format for all columns, providing tabs between fields and a newline character at the end of each row as default terminators. 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, whereby 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.

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

Examining the contents of the Publ.txt data file, two character positions are used for each character in the data, with each field separated by a comma, and each row separated by a newline character.

See Also

ServerBCPDataFileType Property