Search code examples
sqlupdatesdatabase-table

How to Disable/Deny Access to SQL tables while they are updating

I have an SSIS Package that truncate and load tables every day, I need to make sure that no one can access those tables while they are being updated... because he might get partial data.

What is the best method to do that?

Solution

  • It sounds like you need to use an exclusive table lock, start your transaction with the following code (feel free to correct any syntax errors as I'm a postgres/oracle guy)

    SET TRANSACTION ISOLATION LEVEL TABLOCKX
GO
BEGIN TRANSACTION

    do your work here. then commit or rollback.

    for more information see this msdn article