| There are three main ways to initiate bulk-copy operations: -
The bulk-copy program (the BCP utility), a command-prompt utility -
The BULK INSERT statement used in Transact-SQL batches and stored procedures -
The bulk-copy APIs for OLE DB, ODBC, and DB-Library applications These are the potential uses of BCP (in any of its forms): -
Exporting data out of SQL Server tables (to flat files) to other applications and environments -
Importing data in to SQL Server tables (from flat files) from other applications and environments -
Changing the code page of the data already in SQL Server tables (via exporting/importing with special options) -
Restructuring data in tables (exporting data using views or the queryout option, then importing this restructured data back into restructured SQL Server tables) -
Resequencing data in tables that use the identity column (via exporting/importing with special options) -
Re-timestamping data in tables that use the timestamp column (again, via exporting/importing with special options) -
Initiating a new constraint or new insert trigger logic on data in tables for data integrity efforts -
Generating initial snapshots for data replication -
Many others Figure 20.1 illustrates the typical import and export capabilities of BCP. Figure 20.1. Using BCP to import and export data with many environments and for many applications.  However, before utilizing BCP at all, you must first decide whether you should be using BCP or some other mechanism within SQL Server 2000 (because there are alternatives such as Data Transformation Services [DTS]). A few answers to a quick test should make it obvious. There will be two separate tests: one for importing and the other for exporting. First consider the "import" direction. Note: You'll find that deciding to use BCP for exporting is a much simpler decision (fewer questions will need to be answered ). Test: Using BCP to Import Data into SQL Server Tables -
How often is the source data created? In other words, is it a one-time-only load of data or recurring? Assessment: If not very frequent, then it is a BCP candidate. BCP is best used in a batch type environment and not as a method of continuous replication (high frequency). If it is a one-time-only load of data, then it is a BCP candidate. The reason this utility was created in the first place was so that you don't have to write a special program every time you need to load data in to a table from an external source. -
How much data is being imported (loaded) into a table? Assessment: If greater than 500 rows, then it is a BCP candidate. After all, the "B" in "BCP" stands for Bulk. However, even if you are loading one row, BCP could be utilized (to prevent you from having to write a program or doing an insert directly to the table). -
In what physical form is the source data? (Flat files? SQL tables? Other?) Assessment: If a flat file is the source, then it is a BCP candidate. If it is other SQL Server tables, this is a candidate for DTS or some simple cross database Insert with selects. -
What does the source data look like, and how is the source data created? Assessment: If character delimited, or static in format, then it is a BCP candidate. BCP has long been the mechanism of choice for taking data from a flat file and efficiently loading it in to SQL Server tables. It is highly tuned to support this task. In addition, it might be that the source data is an Access DB, a mainframe file or database, or even an Excel spreadsheet. All have the capability to dump their data ("save as" or "export" in some cases) in to a flat file format of some kind (often with character delimiter options). -
Is the source data incremental data (new data rows that are appended, or inserted, to the existing target table)? Assessment: If the source data is truly "new" data and will be appended (inserted) to the existing data in the table, then it is a BCP candidate. BCP does not have an Update Existing Row mode, so it cannot be used in an additive/update situation (where existing column values need to be added to new column values). -
Is the source data a complete refresh of a table's data? Assessment: If the source data will completely re-create the data in the target table, then it is a BCP candidate. Essentially, this will be truncating the table first, followed by the BCP data load of all new rows. -
Are there any image data type columns in the target table? Assessment: If any columns in the target table are of data type image , special care must be taken to deal with them when using BCP. So, this would be a borderline candidate for BCP. The important thing to know is that with SQL Server 2000, it is possible to bulk-copy a data file of image data in to an instance of SQL Server. It just isn't that easy and a better mechanism might be available in SQL Server 2000. By this time, it should be apparent that you have a situation BCP can handle (or not handle) for importing data in to your SQL Server tables. To drive this home, a small example should help. for importing data in to your SQL Server tables. To drive this home, a small example should help. Company X Sales Force Automation Requirement ”Part 1 There's a need to import new order detail data from a Sales Force Automation application's Access database. As you can see in Figure 20.2, each week, each salesperson will dump his or her new orders directly to a comma-delimited flat file and then e-mail them to corporate headquarters. There are 75 salespeople spread throughout the country. This flat file corresponds exactly to the target tables on the main SQL Server 2000 tables. There are no image or identity data types in the target tables. New unique order numbers are automatically assigned by the Access database application. On average, there will be 1,000 new orders per salesperson. Figure 20.2. Sales Force Automation: Access data to SQL Server 2000.  The results of the "import using BCP test" are as listed here: (1) weekly (recurring) source data files, (2) 1,000 new rows per salesperson (75 salespeople), (3) flat files, (4) comma-delimited flat files (that correspond exactly to the SQL Server 2000 target tables, (5) new data only (no updates), (6) not a refresh, and (7) no special data types to be concerned with. Final Assessment: BCP each new file in to the target tables on the weekend as part of batch processing. Now let's consider the "export" direction: Test: Using BCP to Export Data out of SQL Server Tables -
How often is the data to be exported? In other words, is it a one-time-only export of data or recurring? Assessment: If not very frequent, then it is a BCP candidate. Again, BCP is best used in a batch type environment and not as a method of continuous replication (high frequency). If it is a one-time-only export of data, then it is a high BCP candidate. This could possibly eliminate the need for any special program to be written to do this task. -
How much data is being exported out of the table? Assessment: If greater than 500 rows, then it is a BCP candidate. -
What physical format should the exported data have? (Flat files that are tab or comma delimited? SQL tables? Other?) Assessment: If a flat file is the target and this file should be in some standard structure (such as tab or comma delimited), then it is a BCP candidate. If it is another SQL Server table, then this is a candidate for DTS. In addition, it might be that the exported data is to be pulled in to an Access DB, a mainframe file or database, or even an Excel spreadsheet. All have the capability to load data from a flat file format of some kind (often with character delimiter options). -
Are there any image data type columns in the table to be exported? Assessment: If any data columns in the table are of data type image , special care must be taken to deal with them when using BCP. So, this would be a borderline candidate for BCP. The important thing to know is that with SQL Server 2000, it is possible to bulk-copy a data file of image data out of an instance of SQL Server. You must also ask whether the target (receiver) of these files can handle data of this data type. Again, it should be very apparent that you have a situation BCP can handle (or not handle) for exporting data out of your SQL Server tables. Another small example should help here. Company X Sales Force Automation Requirement ”Part 2 There's a need to export complete customer and product data to each salesperson's Sales Force Automation application's Access database (which they have on their laptops) from the central SQL Server 2000 tables. As seen in Figure 20.3, a single set of flat files for the customer data and product data will need to be created as a comma-delimited flat file and e-mailed to each salesperson over the weekend. In turn , each salesperson will need to pull these flat files in to their Access database. There are 75 salespeople spread throughout the country. These flat files correspond exactly to the customer and product tables on the main SQL Server 2000 tables and in the Access database. There are no image or identity data types in the tables. On average, there will be 3,000 customers and 5,000 products. Figure 20.3. Sales Force Automation: Pushing data back out to Access databases from SQL Server 2000.  The results of the "export using BCP test" are as listed here: (1) weekly (recurring) data files, (2) 3,000 customer table rows and 5,000 product table rows, (3) comma-delimited flat files (that correspond exactly to the Access DB), (4) no special data types to be concerned with. Final Assessment: BCP the entire customer and product tables out to comma-delimited flat file, and e-mail them to each salesperson as part of the weekend batch processing. After it has been decided to use BCP, as mentioned before, there are three main ways to initiate bulk-copy operations: -
The bulk-copy program (the BCP utility), a command-prompt utility -
The BULK INSERT statement used in Transact-SQL batches and stored procedures -
The bulk-copy APIs for OLE DB, ODBC, and DB-Library applications NOTE To be able to do any BCP operations, you must first set the SELECT INTO/BULK COPY option to true for the database. There are some issues with this because logging and recovery are affected. It is very common to set this option to true in your batch BCP script and reset it to false after the BCP operations are done. | The following sections outline the primary initiation methods of BCP, BCP's many switches, the format file, and ways to improve performance when using BCP. By the end of these sections, the reader will be able to optimally execute BCP successfully for several common production scenarios. Let's first look at the BCP utility from the command prompt. Later in this chapter, the other initiation methods are discussed. First, do you have the right version of BCP? A quick check of your version of BCP guarantees that you won't run in to any limitations from older versions of BCP that might be left on your servers. You can do this by executing BCP at the command prompt with the “v option and no other parameters. (Note that all BCP switch options are case sensitive; for example, -v and “V are two very different switches.) Here's an example: C:> bcp v BCP - Bulk Copy Program for Microsoft SQL Server. Copyright (c) 1991-1998, Microsoft Corp. All Rights Reserved. Version: 8.00.194 Yes, this is version 8.0 that is distributed with MS SQL Server 2000 (SQL Server 8.0). If a version other than 8.x is present, you must reinstall BCP immediately. At any time, you can see the proper usage and BCP switch options that are available by executing BCP at the command prompt with a question mark ( ? ) only: C:> bcp ? usage: bcp {dbtable query} {in out queryout format} datafile [-m maxerrors] [-f formatfile] [-e errfile] . . . |
