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.




Figure A.


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.




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.




Figure C.


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

Steven Warren
Steven Warren
Steven S. Warren is a popular author residing in Winter Haven, Florida with his wife Danna and 2 children: Catie-Charlotte and Dain. As a columnist on such well-known IT web sites as Techrepublic.com, CNET, and ZDNET, Steven has published numerous articles. Additionally, Steven holds the following certifications: MCDBA, MCSE, MCSA, CCA, CIW-SA, CIW-MA, Network+, and I-Net+. As a Senior Technical Consultant for The Ultimate Software Group, Steven has become an expert at administering Microsoft networks including Microsoft SQL Server. He is also a computer hardware and troubleshooting expert, and is constantly seeking out new technologies and certifications. Additionally, Microsoft recently awarded him the Most Valuable Professional (MVP) award for his outstanding achievements. Steven resides in Winter Haven, Fl.

Latest Articles