How to duplicate all data in a table except for a single column that should be changed.

Question

I have a question regarding a unified insert query against tables with different data structures (Oracle). Let me elaborate with an example:

    tb_customers (
    id NUMBER(3), name VARCHAR2(40), archive_id NUMBER(3)
    )

    tb_suppliers (
    id NUMBER(3), name VARCHAR2(40), contact VARCHAR2(40), xxx, xxx, 
    archive_id NUMBER(3)
    )

The only column that is present in all tables is [archive_id]. The plan is to create a new archive of the dataset by copying (duplicating) all records to a different database partition and incrementing the archive_id for those records accordingly. [archive_id] is always part of the primary key.

My problem is with select statements to do the actual duplication of the data. Because the columns are variable, I am struggling to come up with a unified select statement that will copy the data and update the archive_id.

One solution (that works), is to iterate over all the tables in a stored procedure and do a:

CREATE TABLE temp as (SELECT * from ORIGINAL_TABLE);
UPDATE temp SET archive_id=something;
INSERT INTO ORIGINAL_TABLE (select * from temp);
DROP TABLE temp;

I do not like this solution very much as the DDL commands muck up all restore points.

Does anyone else have any solution?

Answer 1

How about creating a global temporary table for each base table?

create global temporary table tb_customers$ as select * from tb_customers;
create global temporary table tb_suppliers$ as select * from tb_suppliers;

You don't need to create and drop these each time, just leave them as-is.

You're archive process is then a single transaction...

insert into tb_customers$ as select * from tb_customers;
update tb_customers$ set archive_id = :v_new_archive_id;
insert into tb_customers select * from tb_customers$;

insert into tb_suppliers$ as select * from tb_suppliers;
update tb_suppliers$ set archive_id = :v_new_archive_id;
insert into tb_suppliers select * from tb_suppliers$;

commit; -- this will clear the global temporary tables

Hope this helps.

Answer 2

I would suggest not having a single sql statement for all tables and just use and insert.

insert into tb_customers_2 
    select id, name, 'new_archive_id' from tb_customers;
insert into tb_suppliers_2 
    select id, name, contact, xxx, xxx, 'new_archive_id' from tb_suppliers;

Or if you really need a single sql statement for all of them at least precreate all the temp tables (as temp tables) and leave them in place for next time. Then just use dynamic sql to refer to the temp table.

insert into ORIGINAL_TABLE_TEMP (SELECT * from ORIGINAL_TABLE);
UPDATE ORIGINAL_TABLE_TEMP SET archive_id=something;
INSERT INTO NEW_TABLE (select * from ORIGINAL_TABLE_TEMP);