如何复制表中除应更改的单个列之外的所有数据

Question

我有一个关于针对具有不同数据的表的统一插入查询的问题 结构（甲骨文）。让我用一个例子来详细说明：

    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)
    )

所有表中唯一存在的列是 [archive_id]。该计划是通过将所有记录复制（复制）到不同的数据库分区并相应地增加这些记录的 archive_id 来创建数据集的新存档。 [archive_id] 始终是主键的一部分。

我的问题是使用 select 语句来执行数据的实际复制。由于列是可变的，我正在努力想出一个统一的选择语句来复制数据并更新 archive_id。

一种解决方案（可行）是迭代存储过程中的所有表并执行以下操作：

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

我不太喜欢这种解决方案，因为 DDL 命令会弄乱所有还原点。

还有其他人有任何解决方案吗？

Answer 1

为每个基表创建一个全局临时表怎么样？

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

您不需要每次都创建和删除它们，只需将它们保持原样即可。

那么您的存档过程就是一个事务...

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

希望这有帮助。

Answer 2

我建议不要对所有表使用单个 sql 语句，而只使用和插入。

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;

或者，如果您确实需要一个 sql 语句来处理所有这些语句，那么至少要预先创建所有临时表（作为临时表）并将它们保留在适当的位置以供下次使用。然后只需使用动态sql来引用临时表即可。

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);