Truncating sql table question

[hobocamp]

One of our nightly processes is to truncate and re-populate a sql database table for one of our business areas. Our process is to run two jobs - the first job calls a stored procedure to truncate the table, and the second job loads the fresh data.

An instance occurred recently when (for an unrelated reason) the sql server ran out of space. The end result was that our truncate job ran, but the load job was unable to load the refresh data, leaving the user with an empty table.

We were asked if it would be possible to wrap this process up into a single transaction, where the truncate and refresh would function as a single process. I know that option exists in server jobs, but my understanding is that it's no longer possible with enterprise jobs. Just wondering if anyone had run into this issue before, or if anyone has an idea how we might accomplish that.

Thanks for any input.

Tom

[chulett]

You can't, not even in a Server job as a true 'truncate' is not transactional and cannot be rolled back. You would need to do a transactional delete of all of the table data first and then (as you noted) perform the load in the same unit of work for a load failure to roll you back to your original state.