Bulk Insert Without Locking Table

Question

I am using and SSIS packge to insert updated rows into Table A. The data from Table A is being read by other users. How can I insert data and not lock up the table but only lock up the individual rows that are getting updated. 

Answer 1

I believe that the default behaviour for BULK INSERT is not lock the table. However, maybe the option "table lock on bulk load" is set? Check in sys.tables.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

BOL: " Controlling 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. Locking is used by the SQL Server Database Engine to synchronize simultaneous access by multiple users to the same piece of data. When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation. A BU lock allows multiple threads to bulk import data concurrently into the same table while preventing other processes that are not bulk importing data from accessing the table. 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."

http://msdn.microsoft.com/en-us/library/ms180876.aspx

---

Kalman Toth Database & OLAP Architect sqlusa.com
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012