Oracle Populate backup table from primary table

Question

The program that I am currently assigned to has a requirement that I copy the contents of a table to a backup table, prior to the real processing.

During code review, a coworker pointed out that

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

is unduly risky, as it is possible for the tables to have different columns, and different column orders.

I am also under the constraint to not create/delete/rename tables. ~Sigh~

The columns in the table are expected to change, so simply hard-coding the column names is not really the solution I am looking for.

I am looking for ideas on a reasonable non-risky way to get this job done.

Thanks.

Evil.

Answer 1

You could try something like:

CREATE TABLE secondary_table AS SELECT * FROM primary_table;

Not sure if that automatically copies data. If not:

CREATE TABLE secondary_table AS SELECT * FROM primary_table LIMIT 1;
INSERT INTO secondary_table SELECT * FROM primary_table;

Edit:

Sorry, didn't read your post completely: especially the constraints part. I'm afraid I don't know how. My guess would be using a procedure that first describes both tables and compares them, before creating a lengthy insert / select query.

Still, if you're using a backup-table, I think it's pretty important it matches the original one exactly.

Answer 2

Is there a reason that you can't just list out the columns in the tables? So

INSERT INTO backup_table( col1, col2, col3, ... colN )
  SELECT col1, col2, col3, ..., colN
    FROM primary_table

Of course, this requires that you revisit the code when you change the definition of one of the tables to determine if you need to make code changes, but that's generally a small price to pay for insulating yourself from differences in column order, differences in column names, and irrelevent differences in table definitions.

Answer 3

Does the backup table stay around? Does it keep the data permanently, or is it just a copy of the current values?

Too bad about not being able to create/delete/rename/copy. Otherwise, if it's short term, just used in case something goes wrong, then you could drop it at the start of processing and do something like

create table backup_table as select * from primary_table;

Your best option may be to make the select explicit, as

insert into backup_table (<list of columns>) select <list of columns> from primary_table;

You could generate that by building a SQL string from the data dictionary, then doing execute immediate. But you'll still be at risk if the backup_table doesn't contain all the important columns from the primary_table.

Might just want to make it explicit, and raise a major error if backup_table doesn't exist, or any of the columns in primary_table aren't in backup_table.

Answer 4

How often do you change the structure of your tables? Your method should work just fine provided the structure doesn't change. Personally I think your DBAs should give you a mechanism for dropping the backup table and recreating it, such as a stored procedure. We had something similar at my last job for truncating certain tables, since truncating is frequently much faster than DELETE FROM TABLE;.

Answer 5

If I had this situation, I would retrieve the column definitions for the two tables right at the beginning of the problem. Then, if they were identical, I would proceed with the simple:

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

If they were different, I would only proceed if there were no critical columns missing from the backup table. In this case I would use this form for the backup copy:

INSERT INTO BACKUP_TABLE (<list of columns>) 
SELECT <list of columns> 
FROM PRIMARY_TABLE

But I'd also worry about what would happen if I simply stopped the program with an error, so I might even have a backup plan where I would use the second form for the columns that are in both tables, and also dump a text file with the PK and any columns that are missing from the backup. Also log an error even though it appears that the program completed normally. That way, you could recover the data if the worst happened.

Really, this is a symptom of bad processes somewhere which should be addressed, but defensive programming can help to make it someone else's problem, not yours. If they don't notice the log error message which tells them about the text dump with the missing columns, then its not your fault.

But, if you don't code defensively, and the worst happens, it will be partly your fault.