Native Format bcp

The -n parameter (or native value for the DATAFILETYPE clause of the BULK INSERT statement) uses native (database) data types. Storing information in native format is useful when information is to be copied from one computer running Microsoft® SQL Server™ to another. Using native format saves time and space, preventing unnecessary conversion of data types to and from character format. However, a data file in native format cannot be read by any program other than bcp.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using native data format is:

bcp pubs..publishers out publ.txt -n -Sservername -Usa -Ppassword

The bcp utility adds an ASCII character to the beginning of each char or varchar field equivalent to the length of the data in those fields. Noncharacter data in the table is written to the data file in the SQL Server internal binary data format.

Note Native format can now be used to bulk copy data from one computer running SQL Server to another running with a different processor architecture (by means of a data file). This was not possible with earlier versions of SQL Server.

Important Using native mode, bcp, by default, always converts characters from the data file to ANSI characters before bulk copying them into SQL Server, and converts characters from SQL Server to OEM characters before copying them to the data file. This can cause loss of extended character data during the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended characters, use Unicode native format, or specify a code page for the bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT statement).

See Also