ORACLE sql load 数据导入笔记

 

 

 

1、首先，将源数据库的一张表导出为txt文件，以“|”分隔。

 

set head off

set pages 999

set echo off

set feed off

set lines 150

set termout off;

spool  c:/DW_V_USER_CHARGE_SECOND_M_GW.txt  append ;

select * from zhangtao20110120;

spool off;

set termout on;

exit; 

 

2、编写格式化导入文件 mondetailgu|jf_mondet_gu.ctl

 

 

load data 

infile *       

Append into   table   dcust_jf_mondetail_gu

(ACCT_MONTH         terminated by '|', 

ID_NO              terminated by '|', 

GW_TOTAL_FEE       terminated by '|', 

GW_LOCAL_FEE       terminated by '|', 

GW_GN_LONG_FEE     terminated by '|', 

GW_QUJIAN_FEE      terminated by '|', 

GW_GJ_LONG_FEE     terminated by '|', 

GW_GAT_FEE         terminated by '|', 

GW_RENT_FEE        terminated by '|', 

GW_INFO_FEE        terminated by '|', 

GW_WAIBU_FEE       terminated by '|', 

GW_QITA_FEE        terminated by '|', 

GW_DALING_FEE      terminated by '|', 

GW_WWW_FEE         terminated by '|', 

GW_WWW_QITA_FEE    terminated by '|', 

GW_YINYE_FEE       terminated by '|', 

GW_FAV_FEE         terminated by '|', 

GW_OTHER_FEE       terminated by '|')

 

 

3、导入时的shell：mondetailgu|jf_mondet_gu_in.sh

 

 

get_date()

{

sqlplus dbcrmadm/dbcrmadm@crmdb << EOF

select to_char(add_months(sysdate,$1),'yyyymm'),'xxxx' from dual;

exit

EOF

}

 

get_date "-1" | grep xxxx | read dealyymm nouse

echo "${dealyymm}"

 

sqlldr dbcrmadm/密码@SID data=/wxjfjk/WEIXI/MONTH/${dealyymm}/DW_V_USER_CHARGE_SECOND_M_GW.txt  control=/wxjfjk/WEIXISHELL/mondetailgu/jf_mondet_gu.ctl  log=/wxjfjk/WEIXISHELL/mondetailgu/jf_mondet_gu_in.log