Bulk-copy

Bulk-copying into a table on Adaptive Server runs fastest when there are no clustered indexes on the table and you have enabled select into/ bulkcopy. If you have not enabled this option, slow bcp is used for tables with any index or active trigger.

fast bcp logs page allocation only for tables without an index. fast bcp saves time because it does not update indexes for each data insert, nor does it log the changes to the index pages. However, if you use fast bcp on a table with an index, it does log index updates.

fast bcp is automatically used for tables with triggers. To use slow bcp, disable the select into/bulk copy database option while you perform the copy.

To use fast bulk-copy:

  1. Use sp_dboption to set the select into/bulkcopy/pllsort option. Remember to disable the option after the bulk-copy operation completes.

  2. Drop any clustered indexes. Recreate them when the bulk-copy completes.

NoteYou need not deactivate triggers during the copy.

During fast bulk-copy, rules are not enforced, but defaults are.

Since changes to the data are not logged, perform a dump database soon after a fast bulk-copy operation. Performing a fast bulk-copy in a database blocks the use of dump transaction, since the unlogged data changes cannot be recovered from the transaction log dump.