SQL Server™ 2005 Bible

Chapter 19. Performing Bulk Operations

In This Chapter

• Mass inserts from comma-delimited files

• T-SQL ETL processing

• Bulk insert

• BCP

Often, the requirement is to load copious amounts of data quickly—whether it's a nightly data load or a conversion from comma-delimited text files. When a few hundred megabytes of data has to get into SQL Server in a limited time frame, a bulk operation is the way to get the heavy lifting done.

XML's popularity may be growing, but its file sizes seem to be growing even faster. XML's data tags add significant bloat to a data file, sometimes quadrupling the file size or more. For very large files, IT organizations are sticking with CSV (also know as comma-delimited) files. For these old standby files, the best way to insert that data is a bulk operation.

In SQL Server, bulk operations bypass the transaction log and pump data directly to the data file. While this gives bulk operations their speed, the cost of bypassing the transaction log is that it complicates the recovery plan, depending on the recovery model:

• Simple recovery model: No problem, the transaction log is used for current transactions only.

• Bulk logged recovery model: No problem, the bulk operation transaction bypasses the log, but then the entire bulk operation's data is still written to the log.

• Full recovery model: Bulk operations are not recorded to the log, so the log is invalidated. To restart the transaction log recoverability process, following the bulk operation, perform a complete backup ...