What does the -n parameter of the bcp command specify? - The SQL Nomad

Have Database, Will Travel

What does the -n parameter of the bcp command specify?

Tags: , ,

Question: “What does the -n parameter of the bcp command specify?”

Answer: “Use native format.”

The bcp utility is used to perform bulk copies between Microsoft SQL Server and flat data files. It is often used to insert large numbers of new rows into tables, or to run large exports into .csv (or other format) files. In most cases, no knowledge of T-SQL is required, but there usually needs to either be a format file available for the tables involved, or an understanding of those tables’ structure and column data types.

One reason why people might want to use bcp over other import methods is that it can be called from the command line — so a system admin could incorporate a regularly scheduled batch import without having any significant SQL skills, for instance. As with most solutions in the Microsoft Universe, there are lots of ways to skin any cat, and many DBAs will opt for SSIS import solutions, especially when significant data transformations are required, but bcp is an important tool in the SQL server toolbox.

For more details about using it, follow the MSDN link.



-n parameter

The -n parameter tells bcp to use native datatypes when performing the import. This leads to speedier transfer rates, because it avoids having to convert every field into a character format and then back to whatever format the data is supposed to be imported as.  In most cases, this is the preferred method, but a few conditions make using native format impractical or impossible.

Microsoft says that to avoid problems, follow the following rules:

  • The data must be in native format. This means the file does not contain any extended/double-byte character set (DBCS) characters.
  • The target table must be compatible with the data file (having the correct number of columns, data type, length, NULL status, and so forth), or you must use a format file to map each field to its corresponding columns.

Native Data Files

Microsoft has been pretty good about making sure that previous versions of SQL Server are supported with native mode. Per MSDN: “In SQL Server 2016, the bcp utility supports native data files compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.”

Best Practices

There are some suggested best practices that Microsoft suggests when using -c mode with -n native format mode together:

  • (Administrator/User) When possible, use native format (-n) to avoid the separator issue. Use the native format to export and import using SQL Server. Export data from SQL Server using the -c or -w option if the data will be imported to a non-SQL Server database.
  • (Administrator) Verify data when using BCP OUT. For example, when you use BCP OUT, BCP IN, and then BCP OUT verify that the data is properly exported and the terminator values are not used as part of some data value. Please consider overriding the default terminators (using -t and -r options) with random hexadecimal values to avoid conflicts between terminator values and data values.
  • (User) Use a long and unique terminator (any sequence of bytes or characters) to minimize the possibility of a conflict with the actual string value. This can be done by using the -t and -r options.
For more information on importing with native format, see this MSDN page.

Leave a Reply

  • Subscribe!

    Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 348 other subscribers

  • Topics