AARGH! Session timeout!! I'll try and explain again.

I'm not hugely familiar with transactions and rollbacks to be honest. I'm the reporting guy so I when I write a stored proc it's usually a SELECT statement to populate a report. I've recently inherited the data warehouse so I'm looking more into UPDATE and INSERTS etc.

One of the tasks I've got is to rebuild one of the larger fact tables because we've found errors in it. Most of the fact tables are truncated and reloaded from scratch each night but this one is loaded with the previous day's data only. In order to fix the errors the plan is to build another version in parallel with the correct logic then switch over when the rebuild is complete.

I've written a proc that first truncates and loads the new staging table for a given date then calls the procedure to load the fact table before moving on to the next date. I only use BEGIN TRAN when I'm calling the fact table load. My thinking was to compare the row count of the staging table and the fact table then commit the transaction only if the fact count was equal to or lower than the staging count. If it was greater than there might be duplicates so I would roll back the fact load and move on to the next date. If the transaction was rolled back, the proc updates the log table and sends an email.

The update load will only be run during working hours so if there are any problems we'll be able to investigate immediately.

I'm prepared to be told this is a horribly bad approach though.