第二个,merge into 正是为此而设计的第一个 也可以用merge into 也可以 insert into a(A_ID,bb,cc) select B_ID,ee,ff from B where not exists(select 1 from a where a_id=b.b_id)
merge into用法参考下 http://blog.csdn.net/popfei3707/archive/2009/09/01/4505766.aspx 10g以上的版本可以只写when matched和when not matched的其中一段
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); 作用:利用表 b 跟新表a ,条件是a.id=b.id,如果a表中没有该条件的数据就插入。如果你的数据量很大,此sql效率非常高。
也可以用merge into
也可以
insert into a(A_ID,bb,cc)
select B_ID,ee,ff from B
where not exists(select 1 from a where a_id=b.b_id)
http://blog.csdn.net/popfei3707/archive/2009/09/01/4505766.aspx
10g以上的版本可以只写when matched和when not matched的其中一段
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);
作用:利用表 b 跟新表a ,条件是a.id=b.id,如果a表中没有该条件的数据就插入。如果你的数据量很大,此sql效率非常高。
select B_ID,ee,ff from B a left join A b on a.B_ID=b.A_ID and a.ee=b.bb and a.ff=b.cc
where b.A_ID is null
这样就可以
恭祝成功!