All Forums Database
bkj123 3 posts Joined 11/09
27 Sep 2013
backups and timestamps in table names

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)?
Thank you

dnoeth 4628 posts Joined 11/04
28 Sep 2013

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.


bkj123 3 posts Joined 11/09
30 Sep 2013

Thank you Dieter.  Very helpful.

You must sign in to leave a comment.