create or replace procedure xxxxx()isbegin insert into tb(column1,column2....) select column3,colunm4..... from ta minus select column3,colunm4..... from tb;delete from tb where tid=( select tid from tb minus select tid from ta ) commit; end xxxxx;
create or replace procedure asyndata as begin execute immediate 'truncate table ta'; insert into ta select * from tb; commit; end;
1,根据ta表更新tb表; 2,ta表有tb表没有的数据插入tb表; 3,tb表有ta表没有的数据在tb表中删除。1和2可以用merge,网上有很多资料。 3的话可用用delete from tb where no exists (select 1 from ta where ta.tid = tb.tid)
merge into tb b using ta a on(a.tid=b.tid) when matched then update set b.col=a.col..... when not matched then insert into(b.col,....) values(a.col,.....);delete from tb b where exists(select 1 from ta a where a.tid=b.tid);
insert into tb(column1,column2....)
select column3,colunm4.....
from ta minus
select column3,colunm4.....
from tb;delete from tb where tid=(
select tid from tb minus
select tid from ta
)
commit;
end xxxxx;
create or replace procedure asyndata as
begin
execute immediate 'truncate table ta';
insert into ta
select * from tb;
commit;
end;
2,ta表有tb表没有的数据插入tb表;
3,tb表有ta表没有的数据在tb表中删除。1和2可以用merge,网上有很多资料。
3的话可用用delete from tb where no exists (select 1 from ta where ta.tid = tb.tid)
jsjzzh所说的思路我知道,但是我是想各位提供高效的方法。
谢谢
drop 掉tb2
create table tb2 as ...
merge into tb b using ta a on(a.tid=b.tid)
when matched then
update set b.col=a.col.....
when not matched then
insert into(b.col,....) values(a.col,.....);delete from tb b where exists(select 1 from ta a where a.tid=b.tid);