I'm working on re-writing a table load. The current method is a daily job that deletes from the table, then re-loads it. The complaint is that if the load fails the users have no data. The users have said they are ok if the load fails and they have to use yesterdays data.
My solution is to do a table swap.
- Create a staging table that has exactly the same structure, indexes etc
- Then the daily job does the following
- Delete from the staging table
- Load the staging table
- Begin a transaction to keep everyone out of the tables until I'm done
- Rename Live to Backup
- Rename Staging to Live
- Rename Backup to Staging
- Commit the transaction
This works fine on 3 of the 5 tables I'm working with. The other 2 have triggers. Because the trigger has code that references the table name (of course) it can't handle the rename of the object it is attached to.
I start with this:
Table1_Live | -- Trigger for Table1_Live Table1_Stage | -- Trigger for Table1_Stage
After I do my renames I get the following
Table1_Stage | -- Trigger for Table1_Live Table1_Live | -- Trigger for Table1_Stage
The benefits to the method are that the down time for the users is minimal. I have yesterdays data. And if anything goes wrong I roll back to yesterdays data.
My question is does anyone know a better method without the trigger problem, or a way that I can swap my triggers between the two tables so that the code remains correct after the rename?
Oh, and I'm somewhat embarrassed to admit but even though the database is 2005 (we are upgrading soon) the requirement is that the load be done using a DTS package. My plan is for the rest of the process to be done in a stored procedure that can be called from the DTS package (or SSIS if I can ever talk them into it).