

Bad Lapse

I just resubscribed to your magazine after letting my subscription lapse. Bad mistake to let that happen in the first place! I train people in SQL Server administration and programming, and in literally every class, I encounter a question that I can't answer with the default Microsoft documentation. But I can find the answer by searching your Web site. Keep up the good work; SQL Server Magazine is well worth the subscription price and then some!

The ABCs of Bcp

I found Curt A. Gilman's "The ABCs of Bcp" (October 2001, InstantDoc ID 21926) valuable because I regularly have to copy selected tables from one database to another. The size of the database makes it time-consuming to create the new database by using backup-and-restore processes, then removing the unwanted tables. This process also means the resulting database would inherit the transaction log of the source database. Data Transformation Services (DTS) is also impractical because of the work required to maintain relationships and constraints. So after reading Gilman's clear explanations and examples about how to use the bulk copy program (bcp), I created a script that generated two batch files: one to copy the data from each table into text files and one to copy the data from these files to the new database. Everything worked perfectly, or so I thought, until I tried to run the SQL script to create the indexes and constraints on the destination tables (after copying the data to them). When the script tried to generate foreign key references, several errors occurred. Upon investigation, I found that the errors resulted from a couple of primary keys that were based on fields set up as identity fields. When the bcp upload ran, it populated the fields from the identity definition—not from the original values in the source database. I could remove the identity definitions from the destination tables before running the bcp upload, then reapply the identity definitions after the process is finished. Does T-SQL provide a way to remove, then reapply the identity definition, or must I perform this process manually through Enterprise Manager?

Thanks for your feedback; I'm glad the text and bcp examples were helpful. If you have identity fields in your tables, I wouldn't recommend removing the definitions before loading your data. Instead, I would recommend using bcp with the —E switch in the command line when you load your data. The —E switch (note that the letter is a capital E) is the "keep identity values" switch. By default, bcp generates new values for your identity column when you load data. However, the —E switch lets you load the existing values from your file into your identity column without generating new values. The —E switch is an advanced feature that I didn't have room to discuss in my overview article. To learn more about it, along with the many other advanced switches available in bcp, read the section about bcp in SQL Server Books Online (BOL). You can also see a list of available switches by typing bcp on the command line.