组合插入语句

Question

如何将多个语句插入在一起？ 我想插入 8 种不同的描述。例如：

insert into table (
    p_number, description, period
)
select  p.p_number, g.description, g.period, 
from    other_table p
        ,('desc1' as description
                ,2015 + Level as period
        from dual connect by 2015 + Level <=2050) g
where   p.flag = 'Y';

insert into table (
    p_number, description, period
)
select  p.p_number, g.description, g.period, 
from    other_table p
        ,('desc2' as description
                ,2015 + Level as period
        from dual connect by 2015 + Level <=2050) g
where   p.flag = 'Y';

Answer 1

你可以使用Union all

insert into table (
    p_number, description, period
)
select  p.p_number, g.description, g.period, 
from    other_table p
        ,('desc1' as description
                ,2015 + Level as period
        from dual connect by 2015 + Level <=2050) g
where   p.flag = 'Y'
union all
select  p.p_number, g.description, g.period, 
from    other_table p
        ,('desc2' as description
                ,2015 + Level as period
        from dual connect by 2015 + Level <=2050) g
where   p.flag = 'Y';

Answer 2

您可以使用

CTE 生成描述和句点，并将它们交叉连接到实际源表：

insert into target_table (
    p_number, description, period
)
with descriptions (description) as (
            select 'desc1' from dual
  union all select 'desc2' from dual
  -- etc.
),
periods (period) as (
  select 2015 + level
  from dual
  connect by level <= 35
)
select s.p_number, d.description, p.period, 
from source_table s
cross join descriptions d
cross join periods p
where s.flag = 'Y';

当然，未经测试...

Answer 3

使用交叉连接(和联合(：

insert into table (
    p_number, description, period
)
select  p.p_number, d.description, g.period
from    other_table p cross join (
    select 'desc1' description from dual  union 
    select 'desc2' from dual  union 
    ...
    select 'desc1' from dual 
) d cross join ( 
    select 2015 + Level as period
        from dual connect by 2015 + Level <=2050
) g