Utilize BCP with SQL Server 2000

Overview

The Bulk Copy Program (BCP) is a command-line utility that ships with SQL Server 2000. With BCP, you can import and export large amounts of data in and out of SQL Server 2000 databases. Having BCP in your arsenal of DBA tools will add to your skill set and make you a better-rounded DBA. Let’s begin by showing you the syntax of BCP as well as how to effectively use the tool.

BCP 101

The BCP utility is accessed from the command prompt. The syntax 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]

The example starts with bcp followed by a fully qualified table name – database name, table or object owner, table or object name. For instance, if you want to export the authors table, as part of the dbo group from the pubs database, you supply the full table name pubs.dbo.authors. Next, you use an in or out argument to specify whether you want BCP to copy data into or out of a database. You then specify the location of the datafile on your database server.

At this point, our BCP statement looks like this:

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

Remember: When using BCP, do not forget that the switches are case-sensitive. If you do not apply the right case, the BCP statement will not work correctly or will fail.

Next, we will add some of the basic command-line options. The -n switch specifies native SQL Server format. The -c switch is used when formatting the file using Char as a datatype. If you use this switch, you can easily open your file with Excel. The -S switch enables you to add the server/instance name. The -U switch allows you to add the name of the login used to connect to SQL Server. The -P switch lets you add the password of the -U switch, and the -T switch is for establishing a trusted connection to your SQL Server. Figure A shows you what a completed BCP statement looks like.

If we were importing data to the authors table, our BCP command would look like this:

bcp pubs.dbo.authors in c:\temp\authors.bcp -c -Sstevenw -Usa -P

That gives you an idea of the basics of the BCP utility, but there are plenty more switches at your disposal. In all, BCP supports 27 switches. With this many switches available, I recommend that you play around with them to see which ones you might want to use. For example, the -e switch is handy because it will create an error file you can look at if your BCP command returns errors. Here is an example:

bcp pubs.dbo.authors out c:\temp\steventest.txt -n -Sstevenw -Usa -P

-eC:\temp\error.txt

For more information on how to use the various switches, see SQL Server Books Online.

BCP and database objects

Let’s create a new database called pubs2 and use BCP to move all the data from the pubs database into it. First, we need to create the database. Then, we will generate the SQL script, run it on pubs2, and export the data from pubs into pubs2. Let’s Start by creating the database in SQL Server Enterprise Manager and name it pubs2. Next, select pubs, right-click on it, and choose Generate SQL Scripts from the All Tasks menu. Now you are ready to set your scripting options. Click Show All and then select the Script All Objects check box, as shown in Figure B.

Go to the Options tab and select all the check boxes under Table Scripting Options, as we have done in Figure C. You can preview your script from the General tab or click OK to create and save the script.

Recommendation

By performing the above, we are making a complete replica of the Pubs database. We could also have scripted out only one table or multiple tables. You can make the choice, depending on the task at hand. Now, open Query Analyzer and run the newly created script. After the script runs, the database objects and indexes will have been successfully created.

To export the authors table out of pubs and import it into pubs2, we will use the BCP statements we learned earlier:

bcp pubs.dbo.authors out c:\temp\pubauthors.bcp -n -Sstevenw -Usa -P

bcp pubs2.dbo.authors in c:\temp\pubauthors.bcp -n -Sstevenw -Usa -P

Then, open the SQL Query Analyzer and run the following statements on the pubs2 and pubs databases:

Use pubs2


Select * from authors

Select count(*) from authors

Use pubs

Select * from authors

Select count(*) from authors

This allows you to see the full process of moving data come full circle.

Summary

A DBA today may use DTS, Database Restore, and/or attaching and detaching databases to copy data from one server to another. All these tools will get the job done. However, if you want to copy large amounts of SQL Server data quickly, BCP is still one of the best tools available. Once you get past the initial learning curve, you will find that BCP offers a highly efficient way to copy data from one database to another. In my next article, we will explore BCP in SQL Server 2005.

» See All Articles by Columnist Steven S. Warren