• Based on your original statement - I would recommend not even using an explicit transaction. Since this is an import process and you are wiping the data (truncating) and then reloading from the source - using a transaction around the inserts/updates is not very useful.

    Now, if you have to return the system to the state it was in before your process started (on an error, of course) - then you would put the truncate statements inside the transaction also, that way when the statement is rolled back the truncate also gets rolled back.

    Be aware that this type of process can (and probably will) expand your transaction log to a much larger size. It will require the log to be that size to handle all of the transactions - which cannot be marked as reusable space in the transaction log until all of them complete or are rolled back.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs