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

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.

sw_bcp_image001[1]

 

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

Allow Drop and Create in the SQL2014 Table Editor

Q:  I went to change a column to a Primary Key with an Identity specification on a table in SSMS, but when I hit save I got an error message saying the table would have to be dropped then recreated. I’m ok with this. How can I set SSMS to allow drop and create in the SQL2014 table editor?

SSMS_Options_PreventDrop1
A: This is a common question. The fastiduous DBA would say “You shouldn’t have tried to do this in the GUI.” That’s not always practical, though. Developers working in their own development sandbox should have every opportunity to destroy their systems through experimentation, if only to learn how not to do things.

Here’s how you modify SSMS to allow you to drop and create tables when the editor suspects you might not actually want to do that (in the Table Editor).

First, a disclaimer:

Microsoft gives you this warning because it’s real. If the change you’re trying to make to your table is going to result in that table being dropped and recreated, then all of the data in that table is going to be deleted. Don’t delete all the data in your table if you didn’t mean to. Pay attention to warnings. They’re there for a reason.

In the SSMS menu, click on Tools, Options, and then find the Designers option in the tree on the left. Expand that and look for the Table and Database Designers option.

SSMS_Options_PreventDrop3

You’ll see that one of the checked options is “Prevent saving changes that require table re-creation.” Uncheck it, and hit OK. The next time you try to save a table that requires a drop and create, the editor will still warn you, but it will let you override the warning and proceed without further ado.

 

SSMS_Options_PreventDrop4

© 2016 The SQL Nomad

Theme by Anders NorenUp ↑