关于两表之拼接,去重
一、先在测试库中整合好数据
查看数据库的编码格式:
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
保证字符相同,测试库与正式相同
temp dianchang
MEM_ID INTEGER, ID NUMBER not null,
MEM_NAME VARCHAR2(100), DCNAME VARCHAR2(50),
MEM_TYPE VARCHAR2(100), DCTYPE VARCHAR2(20),
TID VARCHAR2(100), ISGZ CHAR(1),
HANGY VARCHAR2(100), NAME_PY VARCHAR2(50),
DCTTYPE VARCHAR2(100), DCQYTYPE VARCHAR2(20),
NAME_PY VARCHAR2(100) DCSNAME VARCHAR2(50)
将正式库中的表temp、dianchang ,选中,右键,date export 导出.dmp文件(相当于export、import命令)
然后在测试库中导入,tools,import table,导入表(先把可能冲突的表清空,别忘了提交)
delete from dianchangnew
或truncate table dianchangnew
二、操作数据
1.建合并后的表dianchangnew,参照dianchang ,view sql
把A无重复、按格式插入dianchangnew
查查dianchang有没有重复
select trim(dcname) from dianchang t
group by trim(dcname)
having count(id)>1
如果没有,将dianchang导入dianchangnew
insert into dianchangnew
select * from dianchang dc
2.自身查重,再导入第二张表(rowid为oracle每列的唯一编号)
insert into dianchangnew(id,dcname ,DCTYPE,NAME_PY,DCQYTYPE)
select t.mem_id, trim(t.MEM_NAME),t.dcttype,t.name_py,t.mem_type from temp_20121024 t
where rowid in (select
max(rowid)
from temp_20121024 t
where trim(t.MEM_NAME) not in (select trim(dn.DCNAME) from dianchangnew dn)
group by trim(t.MEM_NAME)
)
---分组只取最大的id保证自己没有重复的,插入not in不会和new表中冲突。
检查有没有重复的
select trim(dcname) from dianchangnew t
group by trim(dcname)
having count(id)>1
3.导入正式库
做好不用
1.最好别导入导出,前台容易报错,最好复制到excel做下数据新拷贝。
2.添加数据尽快提交,要不表锁住。