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.

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.