I run a number of simple programs in SQL Assistant that insert rows into tables for predictive modeling (I'm a user, not a DBA). Before each insert I make a copy of the target table as a backup. The backup table's name is the target table's name with a "_bu" suffix.
The good side is that I can run these jobs, have a backup, and not have to type in a table name each time for the backup. The bad side is that I only have one backup.
I was considering adding a timestamp column to the backup table and be able to insert multiple copies of the target table. Then, I could delete certain backups periodically. Alternatively, is there a way to copy a table with a name that includes a timestamp (tableA_<timestamp1>, tableA_<timestamp2>, tableA_<timestamp3>)? Are there better approaches? For instance, would you suggest a script (BTEQ)?
Dynamically creating a tablename is not possible without some kind of Dynamic SQL, you need a Stored Procedure for this and your DBA probably didn't grant you this right.
A BTEQ script will need an EXPORT plus RUN FILE or shell scripting and it's an extra step when you use SQL Assistant.
So i would prefer having multiple old versions in a single table.