主页 / 问题 / 1073007
Accepted
dmitriy
Asked:2020-01-22 20:54:57 +0000 UTC2020-01-22 20:54:57 +0000 UTC

按条件插入特定表

  • 772

我想插入这样的东西：

INSERT INTO Table1 (DATE1)
SELECT DATE1
FROM Table2 
WHERE PERIOD_TYPE = 0 AND NEWNUM < 10000;

是否可以根据条件设置目标表的名称Table1：

PERIOD_TYPE = 0 AND NEWNUM < 10000

在WHERE原始表的谓词中Table2？

那是：

INSERT INTO Table3 (DATE1)
    SELECT DATE1
    FROM Table2 
    WHERE PERIOD_TYPE = 1 AND NEWNUM < 10000;
INSERT INTO Table4 (DATE1)
    SELECT DATE1
    FROM Table2 
    WHERE PERIOD_TYPE = 1 AND NEWNUM > 9999;

但是必须进行一种设计，而不是 10 个单独的插入。

sql

1 个回答

  1. Best Answer

    在Oracle DB 中，解决方案是唯一的INSERT ALL（请参阅multi_table_insert）。

    可重现的工作示例

    让我们准备数据 具有不同结构的目标表：

    create table items as
    select rownum id, 'name '||rownum name, date'2020-01-20'+(0.1*rownum) created
    from xmlTable ('1 to 6');

create table target1 as select id, name from items where 0=1;
create table target2 as select name, created from items where 0=1;
create table target3 as select * from items where 0=1;

    插入自身：

    insert all 
when id <= 2 then into target1 values (id, name)
when id >= 5 then into target2 values (name, created)
else into target3
select * from items;

6 rows inserted.

    结果：

    select 'target1' target, id, name, date'1900-01-01' created 
from target1 t union all 
select 'target2' target, 0, name, created 
from target2 t union all 
select 'target3' target, t.* 
from target3 t;

TARGET          ID NAME       CREATED            
------- ---------- ---------- -------------------
target1          1 name 1     1900-01-01 00:00:00
target1          2 name 2     1900-01-01 00:00:00
target2          0 name 5     2020-01-20 12:00:00
target2          0 name 6     2020-01-20 14:24:00
target3          3 name 3     2020-01-20 07:12:00
target3          4 name 4     2020-01-20 09:36:00
    • 5

相关问题