Stored Procedure to identify and copy tables and data from one database to another database on a recurring basis
I need to create a stored procedure that will actually copy the tables and the data in the tables from one database to another database on the same server instance. I need this to be something that is dynamic and recurring. It is for an ETL process to archive the previous days source data before loading the new source data. It is possible that tables will be added or subtracted so I need this to run on variables. I have a stored procedure that will rename tables. That process will rename the Archive tables with the current date in front of them but not until after the new source data has been pulled.
Step1: copy all the tables from Staging into Archive with the same table names
Step2: Truncate tables in Staging and reload that day's source data
Step3: If the source data fails to load correctly in staging then that table name is sent to a logging file and the rest of the container continues to load the Source data.
Step4: If there is a failure within the container then a stored procedure will lookup the name of the table and copy the Archive data for that table back into the Staging.
Step5: Once all source data is loaded into Staging then the Rename procedure I already have will rename the Archive tables to have the day's date and time in front of them.
I cannot seem to get the part of the Copy the tables based on variables to work. I want them to find every table and put a copy of the table and data into Archive.
This is needed very quickly as I have to have the whole process in place this week. This is the only piece I seem to be having trouble with.