select * from abc1 union all select * from abc2 union all select * from abc3;
create table aa1(id int); insert into aa1 values(1); create table aa2(id int); insert into aa2 values(2); create table aa3(id int); insert into aa3 values(3); create table ta(id int);create or replace procedure imp_data is i integer; v_table varchar2(20); v_sql varchar2(100); begin for i in 1..3 loop v_table := 'aa'||to_char(i); v_sql := ' insert into ta select * from '||v_table; dbms_output.put_line(v_table||' '||v_sql); execute immediate v_sql; end loop; commit; end;
使用上面所说的存储过程,表名是要有规律的,没有规律就用 这是不去重复的 用 select * from abc1 union select * from abc2 去重复。
create table tb as (select * from abc1 union all select * from abc2 union all ...) 建议使用union all,至于重复数据的处理,可以押后再做.呵呵
union all
select * from abc2
union all
select * from abc3;
insert into aa1 values(1);
create table aa2(id int);
insert into aa2 values(2);
create table aa3(id int);
insert into aa3 values(3);
create table ta(id int);create or replace procedure imp_data
is
i integer;
v_table varchar2(20);
v_sql varchar2(100);
begin
for i in 1..3 loop
v_table := 'aa'||to_char(i);
v_sql := ' insert into ta select * from '||v_table;
dbms_output.put_line(v_table||' '||v_sql);
execute immediate v_sql;
end loop;
commit;
end;
这是不去重复的
用
select * from abc1
union
select * from abc2
去重复。
建议使用union all,至于重复数据的处理,可以押后再做.呵呵