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 ! ]

 

实例： 

Sql代码 
View Code 
 1 merge into data_complaint_day_test2 s  
 2   using (select seq_id,  
 3                handle_date,  
 4                V_COMPLAINT_TYPE,   
 5                STATISTICS_LEVEL,   
 6                feedback_date,  
 7                node,  
 8                done_date,  
 9                finished_date,  
10                handle_workgroup,  
11                v_dept_name,  
12                v_area,  
13                n_sh_complaint  
14           from data_complaint_day_test t  
15           where t.rowid  in (select max(b.rowid) from data_complaint_day_test b group by (b.seq_id)))  t  
16   on (s.seq_id = t.seq_id )  
17   when matched then   
18     update set   
19       s.handle_date = t.handle_date,  
20       s.service_req_type = t.v_complaint_type,  
21       s.user_level = t.STATISTICS_LEVEL,  
22       s.feedback_date = t.feedback_date,  
23       s.node = t.node,  
24       s.done_date = t.done_date,  
25       s.finished_date = t.finished_date,  
26       s.handle_workgroup = t.handle_workgroup,  
27       s.v_dept_name = t.v_dept_name,  
28       s.v_area = t.v_area  
29   when not matched then   
30     insert ( s.seq_id,  
31              s.handle_date,  
32              s.service_req_type,  
33              s.user_level,  
34              s.feedback_date,  
35              s.node,  
36              s.done_date,  
37              s.finished_date,  
38              s.handle_workgroup,  
39              s.v_dept_name,  
40              s.v_area,  
41              s.n_sh_complaint)   
42     values ( t.seq_id,  
43              t.handle_date,  
44              t.V_COMPLAINT_TYPE,  
45              t.STATISTICS_LEVEL,  
46              t.feedback_date,  
47              t.node,  
48              t.done_date,  
49              t.finished_date,  
50              t.handle_workgroup,  
51              t.v_dept_name,  
52              t.v_area,  
53              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完成

posted @ 2012-09-03 16:24  沙耶  阅读(1215)  评论(0)  编辑  收藏  举报