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.

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.
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , . Bookmark the permalink.

44 Responses to BCP Basics

  1. Brent Ozar says:

    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. Pingback: SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA

  3. SQLDenis says:

    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

  4. 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. :)

  5. Sankar Reddy says:

    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.

  6. Pingback: How can I export data to csv format in SQL Server 2005? | SQLServerPedia

  7. Rajendra says:

    run BCP on client Machine where SQL is not installed

  8. Susan Sheaffer says:

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

  9. Emmanuel says:

    How to use -b parameter in bcp?

  10. Sonia says:

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

  11. Sharon says:

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

    Cheers.

  12. Rob H says:

    Really good post and straight to the point. I was having errors trying to run BCP following other post but your worked first time. Thank you.

  13. Paul M says:

    Hi Michelle,
    Thanks so much for all your work…This is such a great site.

    Thanks Brent Ozar for sending me the link!

  14. Thanks a lot …. I have used some of the parts of your query and modify according to my requirement.

  15. Wincy says:

    Thanks for your explanation, very clear and concise, a great help. Keep up the good work :)

  16. Ashkar says:

    Hi Michelle,

    Thanks a lot, It is really very very useful…

  17. vijay says:

    Awesome this helped pretty much

  18. sim says:

    wonderful explanations….

  19. Faisal says:

    I have visited many sites for BCP but found this info very clear, to the point and helpful
    Thanks for this useful info sharing

  20. CitrusRain says:

    Had a stored procedure that had 3 bcp commands… Full page of code. Wouldn’t work. Was debugging for two whole weeks problem after problem after problem.

    Found this article. Wrote 3 lines of code from knowledge gained from it in 10 seconds flat.

    THANK YOU SO VERY MUCH!!!

  21. Usman Bin Ihsan says:

    Best BCP reference. Simple and concise. Thank you very much.

  22. Sitakanta says:

    Really good for beginner, I’m always writing queries for doing bulk insert through my applications only…
    First time saw this article & tried for the execution, & its executed without any error… Fantastic job :)

  23. Hasan says:

    Dear Professionals, I have one db which is on txt file it has 1 million records i want to import that db into sql server , i have tried above scenario but i could nt get success, when i enter bcp query it ask three things storage type length and field terminator e.g i define name filed ntext = T, 8 for character length, blank for field terminator…. your kind help is required thanks

  24. Pingback: BCP Script Generator « SQL Fool

  25. Caglar says:

    Many thanks, easy sample to understand!

  26. venkat says:

    Good work MICHELLE…. Nice post..

  27. Ivo says:

    Hi Michelle,

    Very nice post, thank you.

    By the way, the CMD supports Copy & Paste, just not only by the Keyboard shortcuts.

    Try to copy something then right click on CMD console -> Paste. There you go!

    Regards

  28. igal israilevich says:

    Thank you very match

  29. Eralper says:

    It will be better if field terminators are also mentioned in the article. I need to use a space character instead of the default tab. I’m not sure if this is possible.

  30. venkat says:

    how to move data to different windows server .path looks like
    //sqlfool.com/2008/12/bcp-basics/. extract from teradata and load into this machine which has sql server

  31. Pingback: Using bcp Utility | :: NickBurns

  32. Eric Bell says:

    Just what I was looking for. I’ve not used BCP before, I have to transfer some data from one Sql Server to another and have limited permissions. I didn’t know if I have permissions for BCP on the remote server SO… I tried a simple export (out) of a very small table according to these instructions. Voila, it worked.

    Then I tried the reverse, again according to these instructions, and it worked too. I believe I can use BCP for this task.

    Really nice, concise article on BCP basics.

  33. Binny Mathew says:

    Simple and concise explanation. Thank you.Binny

  34. Pingback: Less Than Dot - Blog - Awesome

  35. Sachin Rathore says:

    Nice post MICHELLE..Thank you..!!!

  36. maja says:

    Thanks for this Michelle! Keep up the good work!

  37. mir says:

    bcp output to a log file does not send correct exit code.
    when u run “bcp in” through command prompt, exit code is 2 (could not open a file) but when u pipe output to a log file, exit code is 0. Any idea how to get proper exit code?

  38. sri says:

    Thanks Brent.It’s very useful article.

  39. Nishant says:

    Hi Michelle,
    Very Nice Post.

    Can you please help in importing few columns from MS Access to Sql Server.Using the BCP.

    Thanks,
    Nishant

  40. In addition to right clicking to paste, which can be undesirable because it also adds a newline at the end of the what’s in the clipboard buffer, paste is supported in the system from the System Menu.
    The system menu can be accessed by clicking on the icon on the left side of the windows title bar.
    For keyboard only users, that’s Alt + Space, E, P.
    Hold down alt while tapping space, then release alt.
    Then tap E, the accelerator key for the Edit menu item.
    Then tap P, the accelerator key for the Paste menu command.
    With a little practice, it will roll off the tips of your fingers

  41. GregM says:

    Just getting to grips with BCP and this is an excellent post

    Can you tell me how to export a header record containing column names as the first record followed by the exported records

  42. Jomon says:

    Hi,

    On QUERYOUT to csv i am getting one space for NULL values.

    My query is

    ‘bcp “SELECT * FROM dbo02.ExcelTest” queryout ExcelTest.csv -c -t; -T -S LENOVO-PC’

    and getting result as

    1,123.4567,2011-06-17 01:00:00.000,Hello, SQL Server!
    2, , ,Hello, Hi!

    My expected result is

    1,123.4567,2011-06-17 01:00:00.000,Hello, SQL Server!
    2,,,Hello, Hi!

    Please help me to find out the issue.. Thanks in advance..

    Jomon

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>