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 temp (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?