BCP may continue to be a favorite of database administrators because of its great performance and minimal overhead. You will find that import and export processes tend to be very fast and that BCP requires very little memory to operate. BCP does not have a graphical user interface (GUI) and is best used in two situations:

When transferring data to or from SQL Server, BCP uses ODBC.

Tip

Dates are written in ODBC format. You will find that the datetime format is yyyymmdd hh:mm:ss rather than mmm dd yyy hh:mm (A.M./P.M.), and the money format has no commas with four digits after the decimal (instead of commas and two digits after the decimal).

Note

When you import data using BCP, columns with computed values and time stamps are ignored. SQL Server can automatically assign values. To do this, use a format file to specify that the computed values or time-stamp columns in the table should be skipped; SQL Server then automatically assigns values for the column. During export, computed values and time stamps are handled like other values.

Before we examine how to use BCP, let’s look at the command syntax, shown in Example 10-1 and extended in Table 10-1 and Table 10-2. As you can see, the syntax is fairly extensive. BCP switches are case sensitive and order sensitive. You must use these switches exactly as indicated, or you will have problems executing the BCP command.

bcp {[[dbname.][owner].]{tablename | viewname } | "query"}
   {in | out | queryout | format} datafile      
   [switch1 [parameter1]] [switch2 [parameter2]]
   [switchN [parameterN]]

bcp pubs..customer out customers.txt -c -U sa -P"guerilla"    
bcp pubs..customer in customers.txt -f customers.fmt -U sa -P"guerilla"

Table 10-1 provides a summary of key BCP parameters.

BCP also supports a wide variety of switches. These switches and their associated parameters are summarized in Table 10-2.

Although any user can run BCP, only users with appropriate permissions can access SQL Server and the specified database objects. When you run BCP, you can set login information using the U and P switches, or you can use a trusted connection, which is more secure. For unattended operations, it is essential to use these switches to ensure that permissions are granted appropriately. To import data into a table, the user needs INSERT permission on the target table. To export data from a table, the user needs SELECT permission for the source table.

BCP can use three different modes:

The character and wide modes are the best choices when you are copying to a non-SQL Server product. Use native mode when you are copying data between SQL Server tables. These modes all have their strengths and weaknesses. With character or wide mode files, you can view the contents and make sure that you have the right data set, but for imports, you must also tell SQL Server how this data is formatted. You can do this through interactive prompts or by using a format file containing the responses to these prompts. With native mode, you cannot view the contents of native data files, but you do not have to specify data formatting information when importing files either.

You can import data with BCP in two ways. You can start an interactive session, or you can set the necessary responses in a format file. The following example shows how to start an interactive session using a trusted connection:

bcp pubs..customer in customers.txt -T

To specify a format file, use the -f flag, such as in the following example:

bcp pubs..customer in customers.txt -w -f customers.fmt -T

In an interactive session, BCP prompts you for information needed to complete the import or export process. BCP starts an interactive session when either of the following situations occurs:

The interactive session allows you to customize the BCP process, much as you do with a format file. In fact, before you try to create a format file, you should run BCP in interactive mode and then choose to have BCP create the necessary format file for you. This operation will show you the best way to configure the format file.

For each column in a table you are importing, you will see the prompts similar to the following during an interactive session:

Enter the file storage type of field [nchar]:
Enter prefix length of field [0]:
Enter length of field [5]:
Enter field terminator [none]:

Note

Pressing Enter accepts the default values. To skip a column in an import file, type 0 for the prefix length, 0 for the field length, and none for the terminator type. You cannot skip a column when exporting data.

These prompts ask you to type various kinds of information, and in every case the default value for the current column is shown in brackets. At the end of the interactive session, you will be asked if you want to save your responses in a format file. If you answer yes (by typing Y), you can type the name of the format file when prompted, such as:

Do you want to save this format information in a file? [Y/N]
Host filename [bcp.fmt]: customers.fmt

You can then use the format file for other BCP sessions by setting the f switch as explained previously. Because the format file has a rigid syntax that you must follow, I recommend creating a sample file to get started. As Example 10-2 shows, each line in the file contains information fields that determine how data should be imported.

9.0                                                     
50                                                      
1 SQLINT   0 8   ""  1  CUSTOMERID ""                   
2 SQLNCHAR 2 25  ""  2  CUSTNAME   SQL_Latin1_General_CP1_CI_AS 
3 SQLNCHAR 2 20  ""  3  CUSTORG    SQL_Latin1_General_CP1_CI_AS 
..                                                      
50 SQLNCHAR 2 9  ""  3  POSTALCODE  SQL_Latin1_General_CP1_CI_AS

The lines give you the following information:

The lines defining table columns are broken down into fields, and each field sets a different input parameter. Normally, these fields are separated by spaces. The number of spaces does not really matter—provided there is at least one space. BCP treats one or more spaces as a field separator. File format fields operate in the following manner:

When you export data, BCP creates a data file using the name you specify. If you are exporting data from nonnative files (ASCII and Unicode text), the columns in this file are separated with tabs by default, and the last column has a carriage return and newline. You specify a tab as a terminator with and a carriage return and newline with . In a format file, a tab can be an actual tab character or a series of five or more spaces.

As when importing data, you can handle data export interactively. For example, if you start an export session without specifying format information, you are prompted for this information. In the following example, you export a table to a file called customers.txt and use semicolons as the delimiter:

bcp pubs..customer out customers.txt -c -t -T;