Oracle merge basic usage

I used it before for data migration. I found it a little strange when I flipped through the SQL statements today, and it was basically easy to use. Oracle10g the test table and fields (Omitted) are created under Scott by default)

 
MergeIntoMergedept m using (Select * FromDept) dOn(M. deptnom=D. deptno)When NotMatchedThenInsert(M. deptnom, M. dnamem, M. locm)Values(D. deptno, D. dname, D. LOC)
 

Note: insert and update have corresponding syntaxes. Make a record and add it in detail when it is used.

References: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

 

--------- 2.11.27 supplement -----------------------------------------

Today, we use merge to initialize data, with a total data volume of more than 40 W + less than 50 W. Because we sawCodeJDBC is used in initialization, and the result can be imagined, because there is a mess of slow business. Use merge after business analysis. After db_link and synonym are created, the initial code is as follows:

 Merge into base_cmd_info busing (select CZF. * , To_date (substr (CZF. djrq,  0 , 4 ) | '  -  ' | Substr (CZF. djrq, 5 ,2 ) | '  -  ' | Substr (CZF. djrq,  7 , 2  ),  '  Yyyy-mm-dd  '  ) As required _start, to_date (substr (CZF. cjsj,  0 , 4 ) | ' -  ' | Substr (CZF. cjsj, 5 , 2 ) | '  -  ' | Substr (CZF. cjsj,  7 , 2  ),  '  Yyyy-mm-dd  ' ) As create_date from temporaryresidenthouse CZF where CZF. zxbz = '  0  '  And substr (CZF. djrq,  5 , 2 ) In ( 1 , 2 , 3 , 4 , 5 , 6 , 7 ,8 , 9 , 10 , 11 , 12  ) Con (B. PID =C. dah) When not matched theninsert (B. base_pai_info_id, B. county_code, B. required e_code, B. zone_code, B. street_code, B. house_property, B. address, B. performance_state, B. house_type, B. house_structure, B. room_number, B. house_area, B. performance_start, B. else _end, B. host_name, B. host_phone, B. host_address, B. memo, B. is_deleted, B. create_date, B. create_dept, B. create_user, B. update_date, B. update_dept, B. update_user, B. PID) values (base_pai_info_seq.nextval, C. xzdqh, C. PCs, C. jwh, C. JD, C. fwxz, C. czfwdz, C. ZT, C. fwlb, C. fwjg, C. fjs, C. MJ, C. performance_start, null, C. XM, C. lxdh, C. glrdz, null,  '  0  '  , C. create_date, C. PCs, null, C. Dah ); 

However, Oracle10g Reports"Ora-02064Distributed Transaction operations are not supported ". However, this error was not reported before. Later, Google checked out that many people encountered similar problems. The solution is to follow the strict syntax of the document (but the corresponding bug description is not found in the document ). The modified code is as follows. The red part is used in the formatting syntax. The field is irrelevant and does not need to be updated.

 Merge into base_cmd_info busing (select CZF. * , To_date (substr (CZF. djrq,  0 , 4 ) | '  -  ' | Substr (CZF. djrq, 5 , 2 ) | '  - ' | Substr (CZF. djrq,  7 , 2  ),  '  Yyyy-mm-dd  '  ) As required _start, to_date (substr (CZF. cjsj,  0 , 4 ) | '  -  ' | Substr (CZF. cjsj, 5 , 2 ) | '  -  ' | Substr (CZF. cjsj,  7 , 2  ),  '  Yyyy-mm-dd  '  ) As create_date from temporaryresidenthouse CZF where CZF. zxbz = '  0  ' And substr (CZF. djrq,  5 , 2 ) In ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 ,12  ) Con (B. PID = C. Dah)When matched thenUpdate Set B. county_code  = C. xzdqhWhen not matched theninsert (B. base_pai_info_id, B. county_code, B. required e_code, B. zone_code, B. street_code, B. house_property, B. address, B. performance_state, B. house_type, B. house_structure, B. room_number, B. house_area, B. performance_start, B. else _end, B. host_name, B. host_phone, B. host_address, B. memo, B. is_deleted, B. create_date, B. create_dept, B. create_user, B. update_date, B. update_dept, B. update_user, B. PID) values (base_pai_info_seq.nextval, C. xzdqh, C. PCs, C. jwh, C. JD, C. fwxz, C. czfwdz, C. ZT, C. fwlb, C. fwjg, C. fjs, C. MJ, C. performance_start, null, C. XM, C. lxdh, C. glrdz, null,  '  0  '  , C. create_date, C. PCs, null, C. Dah );