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