The bcp utility and BULK INSERT statement accept the TABLOCK hint that allows the user to specify the locking behavior used. TABLOCK specifies that a bulk update (BU) table-level lock is taken for the duration of the bulk copy. Using TABLOCK can improve performance of the bulk copy operation due to reduced lock contention on the table. For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying a table-level lock, execute from the command prompt:
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK"
Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:
BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
TABLOCK
)
If TABLOCK is not specified, the default is to use 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 load.
| Table lock on bulk load | Table locking behavior |
|---|---|
| off | Row-level locks used |
| on | Table-level lock used |
If the TABLOCK hint is specified, the default setting for the table set with sp_tableoption is overridden for the duration of the bulk load.
Note It is not necessary to use the TABLOCK hint to bulk load data into a table from multiple clients in parallel, although doing so can improve performance.
| bcp Utility | sp_tableoption |
| BULK INSERT | Understanding Locking in SQL Server |