之前做数据迁移时候有用过,今天翻了下SQL发现有点陌生,记录下基本使用好了。oracle10g默认scott用户下建立了测试表及字段(略)
MERGE INTO MERGEDEPT M USING (SELECT * FROM DEPT)D ON (M.DEPTNOM = D.DEPTNO) WHEN NOT MATCHED THEN INSERT( M.DEPTNOM,M.DNAMEM,M.LOCM )VALUES( D.DEPTNO,D.DNAME,D.LOC )
说明：insert,update都有相应的语法。做个记录,有在用到的时候在详细补充。
参考资料：
---------2012.11.27补充-----------------------------------------
今天使用merge初始化数据，数据总数40W+不到50W。因为之前看到代码中有用JDBC在初始化,结果可想而知,因为有业务慢的一塌糊涂。分析业务后，使用merge。建立DB_LINK和同义词后,起初的代码如下
MERGE INTO base_rent_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 RENT_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_RENT_INFO_ID,b.COUNTY_CODE,b.POLICE_CODE,b.ZONE_CODE,b.STREET_CODE,b.HOUSE_PROPERTY, b.ADDRESS,b.RENT_STATE,b.HOUSE_TYPE,b.HOUSE_STRUCTURE,b.ROOM_NUMBER,b.HOUSE_AREA,b.RENT_START,b.RENT_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_rent_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.RENT_START,NULL,C.XM, C.LXDH,C.GLRDZ,NULL,'0',C.CREATE_DATE,c.PCS,NULL,NULL,NULL, NULL,c.dah );
但是oracle10g却报“ora-02064不支持分布式事务操作”。但是在以前使用时都没报这种错误。后来google查了下，好多人都遇到类似问题。解决方法就是按照文档严格语法（但是文档中没找到相应BUG的说明）。修改后代码如下。红色部分为格式化语法用的，字段找了个无关的不需更新的字段。
MERGE INTO base_rent_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 RENT_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 THEN UPDATE SET b.COUNTY_CODE = c.XZDQHWHEN NOT MATCHED THENINSERT(b.BASE_RENT_INFO_ID,b.COUNTY_CODE,b.POLICE_CODE,b.ZONE_CODE,b.STREET_CODE,b.HOUSE_PROPERTY, b.ADDRESS,b.RENT_STATE,b.HOUSE_TYPE,b.HOUSE_STRUCTURE,b.ROOM_NUMBER,b.HOUSE_AREA,b.RENT_START,b.RENT_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_rent_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.RENT_START,NULL,C.XM, C.LXDH,C.GLRDZ,NULL,'0',C.CREATE_DATE,c.PCS,NULL,NULL,NULL, NULL,c.dah );