Native Format
The -n switch (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 must be copied from one instance of 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
sql_variant data stored as a SQLVARIANT in a native mode data file maintains all of its characteristics. The meta data recording the data type of each data value is stored along with the data value and is used to re-create the data value with the same data type in a destination sql_variant column. If the data type of the destination column is not sql_variant, each data value is converted to the data type of the destination column, following the normal rules of implicit data conversion. If a data conversion error occurs, the current batch is rolled back. char and varchar values transferred between sql_variant columns may have code page conversion issues. For more information, see Copying Data Between Different Collations.
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.
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. Extended character data can be lost 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).
Nonidentical and Improperly Defined Tables
Using native format to bulk copy data into an improperly defined table can cause the table to be loaded incorrectly. The incorrect loading may appear as an unusual formatting of data in the target table. This also applies to client tools that use the bcp API in native mode.
Native format is intended for high-speed data transfer between identically defined SQL Server tables. To achieve the optimum transfer rate, few checks are performed regarding data formatting. If the table is not defined correctly, use character format.
Correct table definition includes the correct number of columns, data type, length, and NULL status.
Loading ASCII Files
Often, users attempt to load an ASCII file in the SQL Server native format. This leads to misinterpretation of the hexadecimal values in the ASCII file and sometimes the "unexpected end of file" error message. The correct method of loading the ASCII file is to represent each field in the data file as a character string (character format bcp) and let SQL Server do the data conversion to internal data types (for example, int, float, or datetime) as rows are inserted into the table.