Controlling the Locking Behavior for Bulk Import
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.
Note
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.
Command Qualifiers
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 |
Note
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.
See Also
Concepts
Locking in the Database Engine
Other Resources
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
sp_tableoption (Transact-SQL)