After you piece together all the options, your bcp command looks like the following:
bcp Northwind.dbo.Employees out Northwind.txt -c -S HOMER -U sa -P
Now, to reverse the data flow's direction, let's bcp data from a file to a database table by writing a command that uses the following information:
- The source data file is a native SQL Server file type called Invoices.dat.
- The file resides on a server called BART, which you access through a trusted connection.
- The destination table is Invoices, which Lisa owns.
- The table resides in the Northwind database.
Here's the command:
bcp Northwind.lisa.Invoices in Invoices.dat -n -S BART -T
The preceding examples demonstrate some of bcp's basic uses. You can move large tables—indeed whole databases—by using these command-line options. Bcp can also carry out much more elaborate transfers by using fixed-width text files or various delimiters. For more information about bcp command-line options, see BOL.
A Fast Bulk Copy
If the destination database meets two specific conditions, bcp can conduct a fast and efficient bulk copy. First, the database must have the bulk copy database setting enabled. Open Enterprise Manager, drill down to the destination database, right-click the database, and choose Properties from the menu. After the Properties dialog box appears, you can inspect the Options tab to determine whether the Select into/bulk copy option is activated, as Figure 2 shows. Second, the destination tables must be free of indexes. You can also check this condition in Enterprise Manager: Drill down to the destination table, right-click the table, and from the menu, choose All Tasks, Manage Indexes. You can confirm whether a table has indexes by inspecting the Manage Indexes dialog box, which Figure 3 shows. If the destination table is new, you can delay creating indexes until bcp has copied all the data into the database.
When you copy data into a new database, you can usually meet these two conditions without a problem. However, for production databases that applications are currently using, you might encounter a challenge. Because bcp doesn't write its records to the transaction log, you can't reverse any data damage that bcp inflicts through failure or interruption. Therefore, you need to be careful about using bcp to populate tables that applications are accessing, and you might have to consider other options for importing data. You also need to think carefully before changing a production database's database setting. Activating the Select into/bulk copy database option prevents you from restoring any transaction log backups that SQL Server made since the last full database backup. Although you can use bcp to transfer a small group of a database's tables to another database, if you want to make the transfer as fast as possible, the destination database or tables still need to meet the two conditions for fast bulk copy.
As I mentioned earlier, bcp doesn't create database objects. Therefore, to create the tables in the destination database, you have to generate SQL scripts for the source database's table objects. You can easily generate SQL scripts in Enterprise Manager: Drill down to the source database, right-click on the database, and choose Generate SQL Scripts from the All Tasks menu. Then, select the desired database objects from the General tab and check the Formatting tab to confirm that, for each object, Enterprise Manager scripts only the CREATE command. If you also choose to script the DROP command, Enterprise Manager writes a command to drop each object before the command to create it. And although in most cases this script sequence works well, dropping an object manually is safer. Figure 4 shows the Formatting tab in the Generate SQL Scripts window, in which you select the CREATE command scripting option. Next, in the same window's Options tab, verify that Script Indexes, Script Triggers, and Script PRIMARY Keys, FOREIGN Keys, Defaults, and Check Constraints options are activated, as Figure 5 shows. Then, save the SQL scripts to a file by clicking OK. If you want to preview the scripts before you save them, click Preview on the General tab. Finally, to create the required tables and other objects on the destination database, you can execute the SQL scripts that you generated from the source database. I have one caveat: Before proceeding, you need to open Query Analyzer, then execute the CREATE TABLE commands from the SQL scripts that you generated from the source database. Figure 6 shows a CREATE TABLE command in the Query Analyzer window. Group the commands together near the top of the script. At this point in your data transfer, you want to execute CREATE TABLE commands alone because if any of the executed commands create indexes, you won't be able to complete a fast bulk copy into the indexed table objects.
Prev. page
1
[2]
3
next page 
