425
Chapter 16: Programming with T-SQL
16
data need 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 fi le sizes seem to be growing even faster.
XML’s data tags add signifi cant bloat to a data fi le, sometimes quadrupling the fi le size or
more. For large fi les, IT organizations are sticking with CSV (also known as comma-
delimited) fi les. For these old standby fi les, the best way to insert that data is using a
bulk operation.In SQL Server, bulk operations pump data directly to the data fi le according to the follow-
ing models:■ Simple recovery model: The transaction log is used for current transactions only.■ (^) Bulk-logged recovery model: The bulk operation transaction bypasses the log, but
then the entire bulk operation’s data is still written to the log. One complication
with bulk-logged recovery is that if bulk operations are undertaken, point-in-time
recovery is not possible for the time period covered by the transaction log. To regain
point-in-time recovery, the log must be backed up. As extent allocations are logged
for bulk operations, a log backup after bulk operations can contain all the pages from
extents that have been added, which results in a large transaction log backup.
■ Full recovery model: In a full recovery model, bulk operations are not performed;
the engine does full logging of inserts. To restart the transaction log recoverability
process, following the bulk operation, perform a complete backup, and restart the
transaction logs.
For more details on recovery models and how to set them, see Chapter 21, “Backup and Recovery
Planning.” Details on the transaction log are covered in Chapter 47, “Managing Transactions, Locking,
and Blocking.”
Technically, the SELECT INTO syntax is also a bulk-logged operation, and it too bypasses the transaction log.
SELECT INTO creates a table from the results of a SELECT statement; it is discussed in Chapter 12, “Modifying
Data in SQL Server.”
Bulk insert operations are normally one step of an extract-transform-load (ETL) nightly process. Although developing
these ETL processes in T-SQL is perfectly acceptable, Integration Services is a strong alternative and includes bulk
operations. For more details about developing Integration Services solutions, see Chapter 52, “Building, Deploying,
and Managing ETL Workfl ows in Integration Services.”
Bulk operations can be performed with a command prompt using bcp (a command-prompt
utility to copy data to and from SQL Server), within T-SQL using the BULK INSERT com-
mand or using Integration Services.
Bulk Insert
You can use the BULK INSERT command within any T-SQL script or stored procedure to
import data into SQL Server. The parameters of the command specify the table receiving
the data, the location of the source fi le, and the options.
c16.indd 425c16.indd 425 7/30/2012 5:38:12 PM7/30/2012 5:38:12 PM
http://www.it-ebooks.info