For copying data in, the bcp utility is fastest if your database table has no indexes. Fast bcp does not log data inserts in the transaction log.
Important Before a user can do non-logged inserts with bcp, the SA or database owner must first use SQL Enterprise Manager (or the sp_dboption system procedure) to set the Select Into/Bulk Copy option for that database to true. If the option is not set and a user tries to copy data into a table that does not have indexes, SQL Server generates a warning message and will log the bulk inserts.
For a table that has indexes, you do not need to set the Select Into/Bulk Copy option to copy data in, because bulk inserts into tables with indexes are always logged.
When you copy into a table that has indexes, the server logs data inserts in the transaction log, which can cause the transaction log to become very large. After backing up your database with DUMP DATABASE, you can remove the committed transactions from the log by backing up the transaction log to a dump device.
Note If you back up the transaction log with TRUNCATE_ONLY, you will not be able to load any subsequent log dumps unless they have been made after another DUMP DATABASE.
When copying data in, if a target table has no indexes, and if the Select Into/Bulk Copy database option is on, you can use non-logged bcp. Otherwise, you can only use logged bcp.
By default, the Select Into/Bulk Copy option is off in newly created databases. To change the default for all new databases, set this option in the model database.
There is a performance penalty for copying data into a table that has indexes in place. If you are copying a large number of rows, it can be faster to drop all the indexes beforehand with DROP INDEX, set the database option, copy the data into the table, dump the database, and then re-create the indexes. If the bulk data is sorted prior to the copy, you can use the WITH SORTED DATA option to build the clustered index in place on the newly loaded table.