Lock table while inserting Lock table while inserting sql sql

Lock table while inserting


It's true that your correct locking hint affects the source view.

To make it so that nobody can read from the table while you're inserting:

insert into LargeTable with (tablockx)...

You don't have to do anything to make the table look empty until after the insert completes. An insert always runs in a transaction, and no other process can read uncommitted rows, unless they explicitly specify with (nolock) or set transaction isolation level read uncommitted. There is no way to protect from that as far as I know.


BEGIN TRY BEGIN TRANSACTION t_Transaction TRUNCATE TABLE LargeTable INSERT INTO LargeTable SELECT *  FROM viewLargeView  WITH (HOLDLOCK) COMMIT TRANSACTION t_TransactionEND TRY BEGIN CATCH  ROLLBACK TRANSACTION t_TransactionEND CATCH