SQL Server Magazine

Bulk copy program (bcp), the command-line bulk copy utility that ships with all SQL Server releases, is an oft-overlooked but essential component of a DBA's toolkit. Although the utility gets stuck in the SQL Server program folder without so much as a shortcut on the Start menu—and only a brief mention in SQL Server Books Online (BOL)—bcp has survived numerous SQL Server product releases and has even seen minor enhancements in its recent incarnations. Despite bcp's low visibility, it remains one of the best tools for quickly moving large quantities of data into and out of SQL Server databases. When you use bcp properly, it can move large sets of data faster than SQL Server graphical utilities such as Enterprise Manager and Data Transformation Services (DTS), which usually steal the spotlight.

If you're new to bcp, here's an overview of the way it works. Bcp moves data from tables in the database to files on the file system and vice versa. On the file-system side, bcp works mostly with flat files, such as tab-delimited or fixed-width text files, but bcp also works with a special SQL Server file type that allows the transfer of noncharacter-based data. Bcp doesn't create database objects, so a table must exist before you can use bcp to transfer data into it. Typically, when you insert records into a table, the server must first record in a transaction log any changes that you write to database tables, then write the records to the database itself. For large datasets, this operation consumes significant disk time and space because the server must write every modified record twice. However, bcp can import data into a table faster than a logged insert because, under certain circumstances, you don't have to record the modified records in the transaction log. Inserting records into a table without recording changes in a transaction log is called a fast bulk copy, and I describe it in greater detail later. But first, let's delve into bcp's relationship with the command line.

Bcp Basics
Executing utilities from the Windows NT command line is an unfamiliar activity for many DBAs. On Windows 2000 and NT workstations, you can get an NT command line by using the Command Prompt shortcut on the Start menu. DBAs who are accustomed to graphical utilities might find using the command line a little daunting at first, but the rewards are worth the effort. Typing BCP -h at the command prompt presents a cheat sheet for the command line's general syntax, which Figure 1 shows. The cheat sheet includes the program's two-dozen-plus command-line options, but you only need to know a few options to understand the tips I present. Keep in mind that the command-line options are case-sensitive, so, for example, -n doesn't mean the same thing as -N. Generally, the command line's syntax resembles the following example:

bcp {dbtable} {in | out | queryout | format} datafile options

The line begins with bcp, then continues with the fully qualified table name dbtable. A fully qualified table name contains the database name, the table-object owner, and the table-object name. For example, Northwind.dbo.Employees is a fully qualified table name. You then enter the word in or out. If you use bcp to copy data from the file to the database table, you use the in option. If you use bcp to copy data from the database table to the file, you use the out option. The syntax continues with the filename datafile. The filename can be absolute, such as C:\Temp\Northwind.dat, or it can be relative to the current folder, such as ..\Northwind.dat. (The two periods indicate that the file Northwind.dat is written one folder higher in the folder hierarchy.) If you use bcp out, you create a new file with the specified name while automatically overwriting any file of the same name that already exists. You can then specify any number of bcp command-line options.

What kind of command-line options can follow the filename? For starters, you can use the -S option to specify the server name. (You don't need the server name when you connect to a SQL Server installed on a local machine.) If the requested SQL Server instance doesn't respond to the client machine's default protocol, you need to set up the server name as a server alias in the SQL Server Client Network Utility. Next, you can connect to the server by using the -U and -P options to specify a username and password. However, you only need to use these options when you connect under standard security because a trusted connection doesn't require a username or password. If you want to use a trusted connection to the database, you need to choose the -T option to make the connection instead of the -U and -P options. Then, to select the data file type, use the -n option if the data file you want to copy is a SQL Server native type or -c if the file is a tab-delimited text type.

By applying these options, let's write a command that uses the following information to bcp data from a database table to a file:

• The source table is Employees, which dbo owns.
• The table is located in the Northwind database.
• The destination is a tab-delimited text file called Northwind.txt.
• The file is in the current folder on a server called HOMER, which you access through the sa account (no password is required).

   Prev. page   [1] 2 3     next page