Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Controlling the Locking Behavior for Bulk Import

Updated: 5 December 2005

The bcp command, BULK INSERT statement, and INSERT ... SELECT * FROM OPENROWSET(BULK...) statement let you specify that the table is to be locked during a bulk-import operation.

When you specify table locking, a bulk update table-level lock is taken for the duration of the bulk-import operation. Table locking can improve performance of the bulk-import operation by reducing lock contention on the table.

If table locking is not used, the default uses row-level locks, unless the table lock on bulk load option is set to on. Setting the table lock on bulk load option using sp_tableoption sets the locking behavior for a table during a bulk-import operation.

Table lock on bulk import Table locking behavior

Off

Row-level locks used

On

Table-level lock used

If table locking is specified, the default setting for the table set with sp_tableoption is overridden for the duration of the bulk-import operation.

ms180876.note(en-US,SQL.90).gifNote:
It is not necessary to use table-locking to bulk-import data into a table from multiple clients in parallel, but doing so can improve performance.

The following table summarizes the qualifiers for specifying table-locking in bulk-import commands.

Command Qualifier Qualifier type

bcp

-h "TABLOCK"

Hint

BULK INSERT

TABLOCK

Argument

INSERT ... SELECT * FROM OPENROWSET(BULK...)

WITH(TABLOCK)

Table hint

ms180876.note(en-US,SQL.90).gifNote:
In SQL Server 2005, specifying TABLOCK on a table with a clustered index prevents bulk importing data in parallel. If you want to perform a parallel bulk import in this case, do not use TABLOCK. For more information about bulk loading in parallel, see Guidelines for Optimizing Bulk Import.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.