The data for reprocessing requests from the previous week gets moved to a back up table on due to which I am unable to reprocess failures that occurs during the weekend.
For a particular table, we have three different identical versions. Lets say, we have table1 to store the status and id of the documents. So we have three identical versions of Table1.
Table1_A will be active for a week and will store all records and on weekend, a script is run and after that Table1_A will be inactive and table1_B (which is empty) will take its place as active table for the week. Table1_A will retain data from the previous week. The following weekend the script will make Table1_C active, Table1_B will retain its data from the previous week and Table1_A will remove all its data. Same process will repeat every week.
Since the data pertaining to the previous week is in a table that is not active anymore, we are unable to troubleshoot any failures from the previous week.
I have though about copying the previous weeks data from the previous active table to the new active table. The new active table instead of being empty now will have data from previous week.
So, my question is what is a good way to copy this data?
And what are things that I need to worry about while copying this data?
Also, is there a better way to resolve this?
Thanks for your help.