Oracle Populate backup table from primary table

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.

Answers


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.


Need Your Help

Clearing cookies for each test not working. Using Karma + Qunit (using Pavlov) + Ember. Running in PhantomJS/Chrome

javascript cookies ember.js qunit karma-runner

I need to clear a login cookie before each test as previous tests might have stored the cookie. This is what I have right now, but it does not seem to get rid of cookies/credentials as requests for

iOS8 check if device has Touch ID

iphone ipad ios8 touch-id

LAContext has method to check if device can evaluate touch ID and gives error message.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.