Copying Tables with Indexes or Triggers Using bcp

bcp is optimized to load data into tables that do not have associated indexes or triggers. It loads data into tables without indexes or triggers at the fastest possible speed, with a minimum of logging. Page allocations are logged, but row insertions are not.

When you copy data into a table that has one or more indexes or triggers, a slower version of bcp is automatically used, which logs row inserts. This includes indexes that are implicitly created using the unique integrity constraint of a create table command. However, bcp does not enforce the other integrity constraints defined for a table.

Since the fast version of bcp inserts data without logging it, the system administrator or database owner must first set the sp_dboption procedure:

sp_dboption dbname, "select into/bulkcopy", true

If the option is not true and you try to copy data into a table that has no indexes or triggers, the SAP ASE server generates an error message. You need not set this option to copy data out to a file or into a table that contains indexes or triggers.

Note: Because bcp logs inserts into a table that has indexes or triggers, the log can grow very large. You can truncate the log with dump transaction to truncate the log after the bulk copy completes, and after you have backed up your database with dump database.

While the select into/bulkcopy option is on, you cannot dump the transaction log. Issuing dump transaction produces an error message instructing you to use dump database instead.

Warning! Ensure that you dump your database before you turn off the select into/bulkcopy flag. You cannot recover your data if you have inserted unlogged data into your database and you then perform dump transaction before performing dump database.

Unlogged bcp runs slowly while a dump database is taking place.

Comparing fast and slow bcp
	select into/bulkcopy on	select into/bulkcopy off
Fast bcp – no indexes or triggers on target table	Yes – dump transaction prohibited	No – the SAP ASE server forces slow bcp
Slow bcp – one or more indexes or triggers	Yes – dump transaction prohibited	Yes – dump transaction OK

By default, the select into/bulkcopy option is off in newly created databases. To change the default, turn the option on in the model database.

Note: The performance penalty for copying data into a table that has indexes or triggers can be severe. If you are copying in a large number of rows, it may be faster to:

Use drop index (or alter table for indexes) and drop trigger to drop all the indexes and triggers
Set the database option.
Copy the data into the table.
Re-create the indexes and triggers.
Dump the database.

However, you must allocate extra disk space for the construction of indexes and triggers—about 2.2 times the amount of space needed for the data.