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:
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.
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.
Writing a batch file that moves data from one database to
another
Now you know the basic syntax. The following script will show you a basic script structure that can be used to build your migrating tool.
Copy all the code above without the line numbers to your Notepad and save the file as C:\temp\ northwindbuild.bat. Now you are ready to execute it from the command line or schedule it to launch at any time you want. Of course, if you want to run the batch file automatically, you need to hard code all the variables.
Let's look at this batch file. The first three lines of code are used to check if you have provided enough parameters when calling this BAT file. If less then five parameters have been provided, the script will jump to line 20 and prompt you with the message "Usage: NorthwindBuild.bat ServerFrom ServerTo DatabaseTo Username Password." This message means that the batch file is not actually executed. You must provide all the parameters as indicated after "Usage:" Well, of course, you can write your own message that makes sense to you. See Figure 1.
Figure 1
Code lines 4 through 9 are used to prompt you for your inputs. If, for any reason, you did not type in correct value, you could press Ctr-C to exit the process. See Figure 2.
Figure 2
Line 11 copies the table territories out of the NorthWind database to territories.txt. You will need to repeat this line for each table you want to copy. Just remember to change the table name and text file name on each line. By controlling how many lines you write, you can control how many tables you transfer. A powerful feature of bcp is the ability to control the content and data format of each table you want to transfer. To control the content, use a query string instead of a table name in the bcp parameters. For example, you can write the command like this:
bcp "Select * from northwind.dbo.territories where regionid = 3" queryout territories.txt -c -t, -S %1 -U%4 -P%5
Notice that in this command I replaced "northwind.dbo.territories" with "Select * from northwind.dbo.territories where regionid = 3" and "out" with "queryout." As you can see, using a query string instead of a table name gives a lot of control over the content of the data being transferred. You can pick columns, change column orders, and even combine columns here. Be very careful with using such queries. If the database has constraints, such changes might break the database integrity. Also, you need to change the target table structure accordingly, if you changed the source table structure.
Furthermore, if you need perform more complicated data processing, you can call stored procedures instead of using query strings. Here is how to call a stored procedure:
Here, "sp_Myprocedure" is the name for a stored procedure.
Line 13 calls the OSQL utility and executes "DropTerritories.sql." DropTerritories.sql is a text file that contains an SQL command to drop the table territories on the target database. We need a clean table to insert data. We do not want to append data to any existing data. The best choice is to drop the table, then re-create a new one. The other choices, like to delete or truncate a table, are obviously not appropriate in this situation.
Line 15 re-created a table at the target database (CreateTerritories.sql). You need to change the command if you have changed the table structure in line 11.
Line 17 copies all the data from the intermediate text into our target database. Pay attention to the '-E' parameter here, which suppresses the database from automatically generating indexes. I recommend always using this option when migrating a database; otherwise you might find out all your indexes are being replaced by the automatically generated numbers after migrating. If you are importing data that contains special characters or data formats such as '"' or date time, you need to use a format file to handle it.
Line 18 to 22 is just a wrap-up for the script.
This script file is intended to be used against different databases in interact mode. If you need write a batch file for a specific set of databases or you need to schedule the execution, you might want to hard code all the variables.
If you are going to migrate a database with a large amount of data and the transfer time is your concern, consider running the script on the machine where the server is located, or using native format instead of character format as much as possible. Another option is to consider using minimal logged bulk copy, etc. The bcp speed issue is out of the scope of this article, please see references 3, 4, and 5, below, for more detail.
That is it. Isn't that pretty simple? There are other ways, maybe more conventional ways, to handle data migration, such as using DTS, or the backup/restore command, but each of them has its own advantages and disadvantages. Mastering how to write a script data-migrating tool using bcp will certainly add a useful weapon to your development arsenal.
James Huang is a consultant at Electronic Data System (EDS). His specialty is C++, ASP, and Java programming. James Huang is a Sun-certified Java programmer and has a masters degree in computer science. He also has extensive work experience with SQL Sever and Oracle. He can be reached by e-mail at james.huang@eds.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
