Import and Export SQL Server Data From the Command Line With bcp

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 useful tool for command-line aficionados, the bcp utility is a powerful tool for those who seek to insert data into a SQL Server database from within a batch file or other programmatic method. There are plenty of ways to get data into a database, but bcp is the fastest when it is set up with the right parameters.

bcp Syntax

The basic syntax for using bcp is: 

bcp

where the arguments take the following values:

• Table_name — 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 — the full path to the file. For example, you could import the file C:\fruit\inventory.txt.
• Options — allows 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

To put it all together, imagine you have a fruits table in your inventory database and you want to import all 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 produces 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:\> 

You might have noticed 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

You can export data from your database with bcp by changing the direction of the operation from in to out. For example, you can 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.