The existence of indexes on tables affects transfer speed. Unless you explicitly specify fast-logged bcp on tables with indexes, bcp automatically uses slow mode, which logs data inserts in the transaction log. These logged inserts can cause the transaction log to become very large.
To control this data excess and ensure that the database is fully recoverable in the event of a failure, back up the log with dump transaction.
By default, the select into/bulkcopy/pllsort option is false (disabled) in newly created databases. When this option is disabled, bcp automatically uses slow mode. Fast and fast-logged bcp both require that select into/bulkcopy/pllsort option is set to true. To change the default setting for future databases, turn this option on in the model database.
You need not I aset the select into/bulkcopy/pllsort option
to true to copy out data from, or to copy in
data to a table that has indexes. Slow bcp always copies
tables with indexes and logs all inserts.
While the select into/bulkcopy/pllsort option is on, you cannot dump the transaction log. Issuing dump transaction produces an error message instructing you to use dump database instead.
WARNING! Be certain that you dump your database before you turn off the select into/bulkcopy/pllsort flag. If you have inserted unlogged data into your database, and you then perform a dump transaction before performing a dump database, you will not be able to recover your data.
Adaptive Server prohibits dump transaction after running fast bcp. Instead, use dump database. Because slow bcp is a minimally logged operation, Adaptive Server allows you to issue dump transaction after running slow bcp whether select into/bulkcopy/pllsort is set to true or false in the database.
