数据批量导入Oracle数据库

1、tr_copy.ctl

load data
infile 'D:\tr_copy.txt'
into table tr_copy APPEND
(
TROUBLE_CODE      char terminated by '@', 
BRIEF_DESC        char terminated by '@',                      
DESCRIPTION       char terminated by '@',                
PDF_PATH          char terminated by '@',      
LAN_ID            char terminated by '@',                                                           
MODELS            char terminated by '@',                                                 
YEAR              char terminated by '@',                                                 
TYPE              char terminated by '@',
PID               char terminated by '!'
)

 

2、tr_copy.txt

P0010@ VTC油圧バルブ回路異常@@@@@@@625!

 

 

3、打开windows执行cmd命令，进入以上文件所在目录下执行

sqlldr username/password@实例名 control=tr_copy.ctl log=resul.out

 

 

resul.out指执行批量的反馈结果

 

 

 

 

=======

（UltraEdit：^r^n）

merge into tb_trouble_code  aa    
using tr_copy bb          
on (aa.trouble_code=bb.trouble_code)     
when not matched then   
insert values( TROUBLE_CODE_SEQ.nextval, bb.TROUBLE_CODE,'','');

update(select  tc.PID p1,tt.ID p2    from tr_copy tc,tb_trouble_code tt  where tc.trouble_code=tt.trouble_code )
set  p1 = p2;

insert into tb_trouble_code_desc t (
select TROUBLE_CODE_DESC_SEQ.nextval,
c.PID,
c.TROUBLE_CODE,
c.DESCRIPTION,
c.PDF_PATH,
c.LAN_ID ,
c.BRIEF_DESC,
c.MODELS,
c.YEAR,
c.TYPE
from tr_copy c,dual);

======