Sometimes I may add fields to the master table and if I write all the fields explicitely, I will need to modify the stored procedure.  I want to ensure to make an exact copy. So what I want to is:

2- Create a table with the year-month-day postfix

3- Copy all data from the master to the backup table

 

If it's possible to do this within a stored procedure, I will go that way.

If not, I'll do it from a client program.

-Copy the ddl text, change the table name to let's say TblA

-Insert into TblA_2014_04_01 select * from TblA

 

 

How will you handle adding the new fields to existing "backup" tables? How will the procedure know which is a new field and which is not without looking at the structure of all the past tables created?

 

IMHO, creating new tables for each month will become more of a nightmare as time goes by. I would think the better solution would be to create one backup table with a field for the year and month to distinguish the records. Trying to keep up with dynamic DDL changes for an unknown number of tables seem to be more of a pain than it's worth. Then again, maybe I'm just too old school about databases and still believe the best way to control DDL is outside the database, not inside. :)

 

Woody (TMW)