1. Computing

Discuss in my forum

Importing and Exporting SQL Server Data from the Command Line with bcp

Bulk Insert and Export Operations

By

The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method.

bcp Syntax

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 

 Starting copy...

 36 rows copied.
 Network packet size (bytes): 4096
 Clock Time (ms.) Total : 16 Average : (2250.00 rows per sec.)
 C:\> 
If you’re sharp-eyed, you might have noticed that I slipped in two new options on that command line. 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

As I mentioned earlier, 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 

 Starting copy...

 42 rows copied.
 Network packet size (bytes): 4096
 Clock Time (ms.) Total : 1 Average : (42000.00 rows per sec.)
 C:\> 
That’s all there is to the bcp command. You may use this command from within batch files or other programs with access to the DOS command line to automate the import and export of data from your SQL Server database.
  1. About.com
  2. Computing
  3. Databases
  4. SQL Server
  5. bcp: Using bcp to import and export data from SQL Server at the command line

©2014 About.com. All rights reserved.