Export (0) Print
Expand All

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.

Community Additions

ADD
Show:
© 2014 Microsoft