Quick Table Transfers (Imports) using SSIS, Bulk Insert or BCP

Ever wonder why sometimes data transfer can be lightning fast while other times you’re watching sp_who2 wondering when it’s going to finish? It’s likely you’re noticing the difference between minimal logging and full logging. Even in a simple recovery model for a database you can experience row inserts to both the transaction log and the data pages.

The easiest way to take advantage of minimal logging is to set the database recovery model to simple, drop all indexes in the target table then use SSIS, DTS, or BULK INSERT to transfer the data in.

The speed of inserting data in SQL Server is wholly dependent on how many writes occur to the transaction log. These writes occur in two different modes, Minimal logging and Full logging. Minimal logging directly to the data page then writes only a pointer to the datapage in the transaction log, while Full logging writes the content of all the rows to the transaction log prior to inserting them into the data page.

Needless to say, in order to take advantage of quick inserts, you will want to employ minimal logging. There are however a few prerequisites.

• The database recovery model of the target table must be either Simple or Bulk Logged
• If the target table contains a clustered index, it cannot contain data
• A table lock must be aquired on the target table
• The table cannot be part of a replication scheme
• If the table contains a non clustered index, the index itself will be fully logged

The commands that can take advantage of minimal logging are:

• BULK INSERT
• BCP
• SELECT INTO
• CREATE INDEX
• INSERT … SELECT * FROM OPENROWSET(BULK…) (2005)
• INSERT.. SELECT (2008)
• WRITETEXT
• UPDATETEXT

SSIS and DTS use the bulk insert method. Actually SSIS uses an undocumented “INSERT BULK” command, which is a mystery unto itself.

There are some articles on this subject that go into great depth:
Generate Bulk Insert Code with TSQL
SQL Server Storage Engine Developers talk about Bulk Importing