SQL Fool Adventures in SQL Tuning – a blog for the rest of us

BCP Basics

In this blog post, I'm going to walk through the basics of BCP (bulk copy program). BCP is a utility that installs with SQL Server and can assist with large data transfers.

Let's see what parameter options are available to use. From the command line on a machine with SQL Server installed, type "bcp" and press Enter.

You can find out more information on BCP parameters on Books Online: http://msdn.microsoft.com/en-us/library/ms162802.aspx

For now, we're going to examine just the basics. The simplest syntax of a BCP command is:

bcp
databaseName.Schema.TableName *or* "Query"
in, out, *or* queryout
-S ServerName\instanceName
-U userName -P password *or* -T
-c *or* -n *or* specify storage information for each column

Let's look at these options in a little more detail:

databaseName.Schema.TableName *or* Query
You can specify either an entire table to copy or a query. The query should be surrounded in quotations and must also include the fully qualified table name.

in, out, *or* queryout
in = import, out = full table export, queryout = query to select data for export

-U userName -P password *or* -T
You can either specify a specific account to access SQL Server, or use -T to indicate Trusted Connection (i.e. Windows Authentication)

-c *or* -n *or* specify storage information for each column
-c indicates character data type, -n indicates native data type; if neither one is specified, by default you will be prompted for the data type for each column.

Now let's put this together and run some BCP commands. All of these examples will use the AdventureWorks 2008 sample database.

First, let's export an entire table. To do this, we'll use the "out" parameter.

bcp AdventureWorks.Sales.SalesOrderDetail out
C:\bcp_outputTable.txt -SYourServerName -T -c

I don't normally export an entire table... or at least, not in one process. So let's walk through what it would look like to export the same table using a query. This will use the "queryout" parameter.

bcp "Select SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
From AdventureWorks.Sales.SalesOrderDetail" queryout
C:\bcp_outputQuery.txt -SYourServerName -T -c

You'll notice that the total duration for the query was shorter than for the full-table export. This is because we're only exporting a few of the columns. This is important to keep in mind when bcp'ing data: you'll get better performance if you only export the data elements that you actually need.

Now that we've exported some data, let's walk through the process of importing this data. First, let's create a table with a constraint that will result in some errors.

CREATE TABLE dbo.testBCPLoad
(
      SalesOrderID          INT      Not Null
    , SalesOrderDetailID    INT      Not Null
    , OrderQty              SMALLINT Null
    , ProductID             INT      Null
 
    CONSTRAINT PK_testBCPLoad
        PRIMARY KEY CLUSTERED
        (SalesOrderID)
);

Now execute the BCP import command:

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

You should receive a Primary Key error. When you check your results in SQL Server, you should find no results loaded into the table. This is BCP's default behavior.

Let's change our constraint and try the same BCP command again:

ALTER TABLE dbo.testBCPLoad
    DROP CONSTRAINT PK_testBCPLoad;
 
ALTER TABLE dbo.testBCPLoad
    ADD CONSTRAINT PK_testBCPLoad
    PRIMARY KEY CLUSTERED
        (SalesOrderID, SalesOrderDetailID);

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

You should now have the data loaded into your SQL Server destination table:

So there you have it, the basics of BCP!

A few BCP tips:

• BCP commands are case-sensitive!
• If you're accessing the data across a WAN, perhaps via a VPN connection, try to remote desktop (mstsc) to the actual SQL Server to perform the BCP. If possible, keep the operation on the same local drive or even local network as the server; the less distance data needs to travel across a network, the faster BCP will perform.
• If you need to copy large amounts of data (i.e. >100mm rows), try breaking the data into smaller chunks. This will help if you have an error during BCP (i.e. a PK error can rollback the entire import operation by default, although there are options that can change this behavior). When working with partitioned tables, I find it very efficient to segregate the data imported/exported by partition.
• If you're BCP'ing data into a new table, you can minimize impact on the server by waiting to create your indexes after all the data is loaded.
• I like to construct my queries in SSMS, then copy them to BCP. Since the command-line utility does not support copy and pasting, I create a text file with my BCP command in NotePad, then save the command as a .cmd. To execute, just call the .cmd file.