SQL*LOADER将一个数据文件导入多张不同的表

CTRL文件如下例：

LOAD DATA
APPEND
INTO TABLE TABLE_A
WHEN ((1:2) != 'TA')
FIELDS TERMINATED BY '|' trailing nullcols
(
   ATTRIBUTE1 "RTRIM(:ATTRIBUTE1)"
  ,BRANCH_CODE "RTRIM(:BRANCH_CODE)"
  ,CURRENCY_CODE "RTRIM(:CURRENCY_CODE)"
  ,ACCOUNT_CODE "RTRIM(:ACCOUNT_CODE)"
  ,PRODUCT_CODE "RTRIM(:PRODUCT_CODE)"
  ,STATUS "RTRIM(:STATUS)" 
  ,ENTERED_DR "NVL(RTRIM(:ENTERED_DR),0)"
  ,ENTERED_CR "NVL(RTRIM(:ENTERED_CR),0)"
  ,COUNT_DR "NVL(RTRIM(:COUNT_DR),0)"
  ,COUNT_CR "NVL(RTRIM(:COUNT_CR),0)"
  ,FILLER "RTRIM(:FILLER)" 
  ,INTRIM_ID "${DATA_SEQUENCE}.NEXTVAL"
  ,CONTROL_ID CONSTANT '${CTL_ID}'
  ,CREATED_BY CONSTANT '${USER_ID}'
  ,CREATION_DATE SYSDATE
  ,LAST_UPDATED_BY CONSTANT '${USER_ID}'
  ,LAST_UPDATE_DATE SYSDATE
  ,LAST_UPDATE_LOGIN INTEGER "USERENV('SESSIONID')"
)
INTO TABLE TABLE_B
WHEN ((1:2) = 'TB')
FIELDS TERMINATED BY '|' trailing nullcols
(
   FILLER POSITION(1) "RTRIM(:FILLER)"
  ,REC_COUNT "NVL(RTRIM(:REC_COUNT),0)"
  ,ACCOUNTING_DATE "TO_DATE(RTRIM(:ACCOUNTING_DATE),'YYYYMMDD')"
  ,TOTAL_ENTERED_DR "NVL(RTRIM(:TOTAL_ENTERED_DR),0)"
  ,TOTAL_ENTERED_CR "NVL(RTRIM(:TOTAL_ENTERED_CR),0)"
  ,CONTROL_ID CONSTANT '${CTL_ID}'
  ,CREATED_BY CONSTANT '${USER_ID}'
  ,CREATION_DATE SYSDATE
  ,LAST_UPDATED_BY CONSTANT '${USER_ID}'
  ,LAST_UPDATE_DATE SYSDATE
  ,LAST_UPDATE_LOGIN INTEGER "USERENV('SESSIONID')"
)

注意后面的表一定要指定POSITION(1) 才能决定数据从第一列开始读取，否则会出现空值或数据列错位。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7563769/viewspace-661843/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7563769/viewspace-661843/