Bulk inserting millions of rows into staging tables sounds simple—until row-level locking and full transaction logging turn it into a major pipeline bottleneck.

What is TABLOCK?

TABLOCK is a table-level lock hint. While row-level locking is great for concurrency, it is expensive for massive ETL jobs. By using TABLOCK, you tell SQL Server to take a single lock on the entire table.

Why does it make Inserts faster?

  • Minimal Logging: When used with a SELECT INTO or an INSERT INTO ... SELECT on a heap (a table without a clustered index), TABLOCK allows for “Minimal Logging,” which significantly reduces I/O.
  • Reduced Lock Overhead: The engine doesn’t have to manage millions of individual row locks.
  • Parallelism: In some configurations, it allows multiple threads to write to the table simultaneously.Since it locks the whole table, other users won’t be able to write to it until your job is done

The Command:

INSERT INTO TargetTable WITH (TABLOCK)
SELECT * FROM SourceTable;