Table Locking durint Truncate/Insert

  • Hi,

    Every week I want to repopulate a table by doing something along the lines of:

    TRUNCATE TABLE tblA

    INSERT INTO tblA SELECT ... FROM tblB

    That works fine (and takes under five minutes) but we also have an automatic process running that sends data from a view looking at tblA to another server. We don't know when this process will run so need to be sure that tblA will always be populated and that this process doesn't kick off in between the TRUNCATE and the INSERT or halfway through the INSERT etc.

    Is it as simple as putting the TRUNCATE/INSERT into a transaction? If so, in the extremely unlikely event that the automatic process kicks off, will it just wait until the transaction completes and the tblA is populated?

    Many thanks,

    Richard

    _____________________________________________________________________
    MCSA SQL Server 2012

  • Richard Warr (10/13/2011)


    Hi,

    Every week I want to repopulate a table by doing something along the lines of:

    TRUNCATE TABLE tblA

    INSERT INTO tblA SELECT ... FROM tblB

    That works fine (and takes under five minutes) but we also have an automatic process running that sends data from a view looking at tblA to another server. We don't know when this process will run so need to be sure that tblA will always be populated and that this process doesn't kick off in between the TRUNCATE and the INSERT or halfway through the INSERT etc.

    Is it as simple as putting the TRUNCATE/INSERT into a transaction? If so, in the extremely unlikely event that the automatic process kicks off, will it just wait until the transaction completes and the tblA is populated?

    Many thanks,

    Richard

    I am sure someone will correct me if I am wrong here, but I think you have the right idea although I think you may want to change your truncate table to a delete from tblA inside of a transaction. As long as the delete and the insert are inside of a transactin the transaction will block the other process until it is finished. Now the other process might timeout while it is waiting. You would also want to make sure the other process isn't using a (nolock) hint which would do a dirty read.

  • Thanks for the reply. I was using the TRUNCATE purely for speed as the table may contain 10 million records in a few months time. There's also less of an impact on the log files.

    However, I'll happily change that to a DELETE if it makes things safer.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • My concern about the truncate has to do with the truncate command does not log the removing of the records. The truncate command is logged and you can roll it back. Still since the size of your table is so large it would seem best to truncate, so that it is quicker and doesn't take up log space.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply