BCP Basics

December 1, 2008 by Michelle Ufford
Filed under: Performance & Tuning, SQL Tips 

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.

BCP Parameters

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

 

Export Table with BCP

Export Table with BCP - Results

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

 

Export Query with BCP

Export Query with BCP - Results

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

 

Load Data with BCP

Load Data with BCP - Error

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.

Check Destination Table

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:

Import Data with BCP - Results

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.

Tags: ,

Comments

12 Comments on BCP Basics

  1. Brent Ozar on Tue, 2nd Dec 2008 7:08 am

    2. Great summary of what it does without doing a copy/paste of BOL! I’m definitely adding this one to my weekly links list.

  2. Michelle Ufford on Tue, 2nd Dec 2008 7:19 am

    3. Thank you, Brent! :)

  3. SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA on Fri, 5th Dec 2008 6:01 am

    4. [...] BCP Basics – the SQL Server Bulk Copy utility is one of those tools I only have to use once or twice a year, and I never remember how it works.  Michelle does a great job of summing up what you need to know without doing a copy/paste from Books Online. [...]

  4. SQLDenis on Fri, 5th Dec 2008 7:31 am

    5. Also don’t forget that you need to specify TABLOCK if you really want it to be minimally logged. Take a look at the -h argument

    Denis Gobo

  5. Michelle Ufford on Fri, 5th Dec 2008 7:43 am

    6. Thanks, Denis, great point! I plan to follow this post with another one on intermediate BCP, i.e. error handling, loading identity values, etc., and I’ll be sure to include that. :)

  6. Sankar Reddy on Sun, 11th Jan 2009 3:58 am

    7. Michelle,

    Most of the times when explaining about BCP, everyone leaves out the option of executing a SP from BCP. Its not even mentioned as a demonstration also. May be you can add this if it is ok with you.

  7. How can I export data to csv format in SQL Server 2005? | SQLServerPedia on Thu, 29th Jan 2009 12:27 pm

    8. [...] BCP Basics on SQLFool.com Share This Post: [...]

  8. Rajendra on Wed, 29th Apr 2009 4:55 am

    9. run BCP on client Machine where SQL is not installed

  9. Susan Sheaffer on Tue, 14th Jul 2009 10:11 am

    10. HOW DO I ??? BCP out data from a table that has a text data type column.
    I want to replace the carraige return line feed ascii(char(10)) with a litteral . The purpose is to have a text file that I can use with SQL loader to load to an Oracle table. Then replace the with an ascii(char(10)).

  10. Emmanuel on Sun, 13th Sep 2009 6:35 pm

    11. How to use -b parameter in bcp?

  11. Sonia on Mon, 21st Sep 2009 1:37 pm

    12. with the BPC utility how I can add the name of the columns in the text file?

  12. Sharon on Sun, 4th Oct 2009 7:33 pm

    13. Thanks Michelle, this helped heaps. MSDN was too complicated.
    I got what I wanted from your guide.

    Cheers.

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.