The Bulk Copy Program (BCP) is a powerful command-line utility that comes with SQL Server. It allows you to import and export large amounts of data in and out of SQL Server databases. In this article, we will explore the syntax and usage of BCP, as well as its benefits for database administrators.

BCP 101

To use BCP, you need to access it from the command prompt. The basic syntax of BCP is as follows:

bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]

Let’s break down the syntax:

• {dbtable | query}: This specifies the table or query you want to import or export data from.
• {in | out | queryout | format}: This specifies whether you want to copy data into or out of a database.
• datafile: This is the location of the file on your database server.
• -n: This switch specifies the native SQL Server format.
• -c: This switch is used when formatting the file using Char as a datatype.
• -S: This switch enables you to specify the server/instance name.
• -U: This switch allows you to specify the login used to connect to SQL Server.
• -P: This switch lets you specify the password of the login used.
• -T: This switch is for establishing a trusted connection to your SQL Server.

For example, to export the authors table from the pubs database, the BCP command would look like this:

bcp pubs.dbo.authors out c:\temp\authors.bcp

Remember that the switches are case-sensitive, so make sure to use the correct case for each switch.

Exploring BCP Options

BCP supports a wide range of switches, offering flexibility and control over the data import and export process. For example, the -e switch is useful as it creates an error file that you can refer to if your BCP command encounters any errors.

It is recommended to experiment with the various switches to understand their functionality and determine which ones are most suitable for your specific requirements.

BCP and Database Objects

BCP can be used to move data between databases. Let’s say we want to create a new database called pubs2 and copy all the data from the pubs database into it. We can achieve this using BCP along with SQL scripts.

First, we need to create the pubs2 database. Then, we can generate a SQL script for the pubs database and run it on pubs2. Finally, we can export the data from pubs and import it into pubs2 using BCP.

By following this process, we can create a complete replica of the pubs database in pubs2. However, it is also possible to script out only specific tables based on your requirements.

Summary

While there are various tools available for copying data between SQL Server databases, the Bulk Copy Program (BCP) remains one of the most efficient options for handling large amounts of data. Once you become familiar with its syntax and options, BCP can significantly enhance your data import and export capabilities as a database administrator.

In our next article, we will explore the advancements and features of BCP in SQL Server 2005.