Bulk Copy Program (bcp), the command-line utility that ships with all SQL
Server releases, is an essential component of a DBA's toolkit. This command-line
program moves data between an existing database table or view and an
operating-system file stored in ASCII or machine format. Articles that discuss
this utility are rare. It is known to all that bcp is less capable than the Data
Transformation Service (DTS) in handling data format issues. However, for those
who love script programming, who like to have total control over the
data-migrating process, and who want to speed up the data-transfer process, it
is really a simple to use, flexible, and powerful tool. Use it to migrate data
from one database to another, or just to import data from text files.
In a recent project assignment we needed to move a development database to a
production server. The requirement specifications for this migration were:
- Move a portion of the development data to a production server, which
includes moving all products data, part of the users' information data, and
excluding all testing data.
- Add a list of new user information to the production database. This
information could come from a legacy system and/or MS Excel files.
- The development database should remain intact so that the development team
can use it without being affected by the migration.
We decided to write a batch file using bcp to do the job. The result proved
to be very satisfactory. In this article, I will describe the database migrating
batch file and show you how to use bcp.
System requirements and Basic Syntax of bcp and
osql
1. System requirements
I am assuming readers of this article understand basic database concepts,
such as user, roles, and permissions, as well as SQL syntax.
In order to write such a batch file there are two utilities you need to know.
The first one is bcp, which is available on every machine that has MS SQL Server
or client installed. The second is osql, pronounced "OH-Sequel," a utility that
comes with NT or the Windows 9x operating system. The osql tool is a commonly
used utility that interacts with SQL databases from the command line. I will not
discuss osql in detail, except for the syntax used in our batch file. Besides
these two utilities, you also need to have a valid database username and
password, unless you use trusted connections, which will not be discussed in
this article. Obviously, the login ID you use within bcp command must have
appropriate operating permissions on the object(s) that you will access.
2. Basic syntax: [1]
To export data from an SQL server table, the basic bcp command you can use
is:
bcp databasename.ownername.tablename out
datafile -t fielddelimeter -c -U username -S servername
where
databasename is the name of the SQL Server database in which
the table you are exporting resides. You can skip this entry if the table you
are exporting is in your default database.
ownername is the name of
the table owner. You can skip this entry if you own the table you are
exporting.
tablename is the name of the table you are
exporting.
datafile is the full path in which you want to save the
file, such as C:\test.txt.
fielddelimeter is the character you want to
use as the field delimiter. Typically, you'll want to use a comma. However, if
the record you are exporting has commas within the data, then you may want to
use a special character, such as "\t" or "@".
username is your SQL
Server login user ID. (This may be the same as your NT Server login user ID if
your SQL Server is set up to accept your NT Server login ID automatically as
your SQL Server login ID.)
servername is the name of your SQL Server.
The -S servername parameter is only relevant if you are running the bcp utility
from a remote computer. If you are running bcp directly from the NT server, skip
the -S servername entry.
-c indicates that the operation uses
character data types. A tab character is assumed as the field delimiter and the
new line character is the row separator. You could use -w instead of -c if you
want to use Unicode data types. You need to use a format file if you do not use
the -c or -w option in your command. Using a format file will add a lot of
data-format processing power to bcp, but this is a more advanced topic and will
not be covered in this article.
Now let's look at an example that will copy the table territories to
territories.txt, with tabs separating each field.
bcp Northwind.dbo.territories out
C:\territories.txt -c -t\t -Usa -Ppwd -Sservername
Here, Northwind is a sample database that comes with MS SQL server. You need
to replace the servername with the name of the database you are going use
and sa, pwd with real username and password. The syntax for
importing a text file to a database table is exactly the same as exporting,
except for replacing the "out" with an "in." Here is a sample:
bcp Northwind.dbo.territories in
C:\territories.txt -c -t\t -Usa -Ppwd -Sservername
This command will bulk insert all the data in territories.txt to the table
territories.
The syntax for using osql is very similar to bcp. If you are interested, you
may find out all the parameters for this utility by typing "osql /?" on the
command line. Here I list only the parameters we are using in this article.
osql -Sservername -ddatabasename -Uusername
-Ppassword -n -i filename
where
-n means remove numbering
-i means input file. It
should be followed by a file name with a full path. If no path is specified, the
file must be located in the current directory or the same directory as the batch
file. You can also use -Q or -q options, which is followed by a query string.
Remember that all the parameters in osql and bcp are case sensitive.
