1. 建立DBLINKOracle DB Link http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4698642.aspx2. 使用merge into Oracle merge into 的用法详解实例作用:merge into 解决用B表跟新A表数据,如果A表中没有,则把B表的数据插入A表; 语法: MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND [...]...) WHEN MATHED THEN [here you can execute some update sql or something else ]WHEN NOT MATHED THEN [execute something else here ! ]-------实例------- merge into a using (select id,name from b ) c on(a.id=c.id ) when matched then update set a.name=c.name when not matched then insert (a.id,a.name) values (c.id,c.name); 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4714921.aspx ------------------------------------------------------------------------------ Blog: http://blog.csdn.net/tianlesoftware 网上资源: http://tianlesoftware.download.csdn.net 相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx Q Q 群:62697716
create table XXX as select * from xxx_1@db_link; drop table xxx; alter table xxx_1 rename xxx;
这个方法与6楼的方法比,哪个好些,哪个快. --清除本库中有差异ID的记录,为覆盖数据做准备 delete from B.hawk@DBLink where id in (select Id from (select * from A.hawk minus select * from B.hawk@DBLink)); --覆盖数据 insert into B.hawk@DBLINK select * from (select * from A.hawk minus select * from B.hawk@DBLink) where Id Not in(select Id from B.hawk@DBLINK);这个方法也可以,但是因为A和B的字段是145个,数据是100W行,只运行比较select * from A.hawk minus select * from B.hawk@DBLink就得150s,速度有些慢,而且这还是一张表,还有20多张表,想问下,这个速度快,还是6楼的方法的速度快.谢谢啦.这个好处就是省去了写每个字段的方法.谢谢大家.
要sql只要查那张表就好了。不返回多好啊,一个包含merge into的procedure+job就搞定。。
http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4698642.aspx2. 使用merge into
Oracle merge into 的用法详解实例作用:merge into 解决用B表跟新A表数据,如果A表中没有,则把B表的数据插入A表;
语法:
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]WHEN NOT MATHED THEN [execute something else here ! ]-------实例-------
merge into a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name);
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4714921.aspx
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
as
select * from xxx_1@db_link;
drop table xxx;
alter table xxx_1 rename xxx;
--清除本库中有差异ID的记录,为覆盖数据做准备
delete from B.hawk@DBLink where id in
(select Id from (select * from A.hawk minus select * from B.hawk@DBLink));
--覆盖数据
insert into B.hawk@DBLINK select * from (select * from A.hawk minus select
* from B.hawk@DBLink) where Id Not in(select Id from B.hawk@DBLINK);这个方法也可以,但是因为A和B的字段是145个,数据是100W行,只运行比较select * from A.hawk minus select * from B.hawk@DBLink就得150s,速度有些慢,而且这还是一张表,还有20多张表,想问下,这个速度快,还是6楼的方法的速度快.谢谢啦.这个好处就是省去了写每个字段的方法.谢谢大家.
merge into 在大数据量的情况下,才能看出效率,绝对比你的快。