作用: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 data_complaint_day_test2 s
using (select seq_id,
handle_date,
V_COMPLAINT_TYPE,
STATISTICS_LEVEL,
feedback_date,
node,
done_date,
finished_date,
handle_workgroup,
v_dept_name,
v_area,
n_sh_complaint
from data_complaint_day_test t
where t.rowid in (select max(b.rowid) from data_complaint_day_test b group by (b.seq_id))) t
on (s.seq_id = t.seq_id )
when matched then
update set
s.handle_date = t.handle_date,
s.service_req_type = t.v_complaint_type,
s.user_level = t.STATISTICS_LEVEL,
s.feedback_date = t.feedback_date,
s.node = t.node,
s.done_date = t.done_date,
s.finished_date = t.finished_date,
s.handle_workgroup = t.handle_workgroup,
s.v_dept_name = t.v_dept_name,
s.v_area = t.v_area
when not matched then
insert ( s.seq_id,
s.handle_date,
s.service_req_type,
s.user_level,
s.feedback_date,
s.node,
s.done_date,
s.finished_date,
s.handle_workgroup,
s.v_dept_name,
s.v_area,
s.n_sh_complaint)
values ( t.seq_id,
t.handle_date,
t.V_COMPLAINT_TYPE,
t.STATISTICS_LEVEL,
t.feedback_date,
t.node,
t.done_date,
t.finished_date,
t.handle_workgroup,
t.v_dept_name,
t.v_area,
t.n_sh_complaint);
where t.rowid in (select max(b.rowid) from data_complaint_day_test b group by (b.seq_id))
因为在data_complaint_day_test 表中会出现seq_id字段相同的记录,这里只取一条
上面代码是一个存储过程的部分代码。
测试效果:10万条数据,15s完成