The Bulk Copy Program (BCP) is a command-line utility that allows administrators to import bulk data into a table or export it from a table into a file. It also supports several options that define how data will be exported, where it will be imported and which data will be loaded. The BCP utility can quickly transfer large amounts of data.
The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp utility performs the following tasks:
• Bulk exports data from a SQL Server table into a data file.
• Bulk exports data from a query.
• Bulk imports data from a data file into a SQL Server table.
• Generates format files.
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
The basic syntax for using bcp is:
bcp <table_name> <direction> <file_name> <options>
Where the arguments take the following values:
• table_name is the fully qualified name of the table. For example, you might use inventory.dbo.fruits to insert records into the fruits table, owned by the database owner, in the inventory database.
• direction indicates whether you want to import (“in” direction) or export (“out” direction) data.
• file_name is the full path to the file. For example, you could import the file C:\fruit\inventory.txt.
• options allow you to specify parameters for the bulk operation. For example, you can specify the maximum number of errors allowed with the –m option. You may also use the –x option to specify an XML file format. Consult Microsoft’s bcp documentation for a full list.
BCP Import Example
Let’s put it all together. Imagine that you have a fruits table in your inventory database and that you want to programmatically import all of the records from a text file stored on your hard drive into that database. You would use the following bcp command syntax:
bcp inventory.dbo.fruits in “C:\fruit\inventory.txt” -c -T
This would produce the following output:
C:\>bcp inventory.dbo.fruits in “C:\fruit\inventory.txt” -c -T
36 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (2250.00 rows per sec.)
• The –c option specifies that the file format of the import file will be tab-delimited text with each record on a new line.
• The –T option specifies that bcp should use Windows authentication to connect to the database.
BCP Export Example
You can export data from your database with bcp by changing the direction of the operation from “in” to “out”. For example, we could dump the contents of the fruit table to a text file with the following command:
bcp inventory.dbo.fruits out “C:\fruit\inventory.txt” -c -T
Here’s how that looks on the command line:
C:\>bcp inventory.dbo.fruits out “C:\fruit\inventory.txt” -c -T
42 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (42000.00 rows per sec.)
Consider following tips while operating the BCP:
• 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.