Home » RDBMS Server » Server Utilities » Loading data into multiple tables
| Loading data into multiple tables [message #201888] |
Tue, 07 November 2006 04:06  |
laksha
Messages: 42
Registered: June 2006
|
Member |
|
|
I want to load data from one file delimited by ^ into multiple tables.
The data is to be loaded in different tables using the 2nd field in the file.FH,BH,CD,DD
Each corresponding records in the file have different stucture and each table is created accordingly.
The problem I am facing is that the records satisfying the first when clause are loaded into the corresponding table. But for all the other when clauses the records are discarded because of "failed when clauses".
Attaching table creation scripts,file(attched),control file,parfile
My control file is here
LOAD DATA
INFILE 'ABCD.txt'
APPEND
INTO TABLE LAXMAN_ETDS_FH
WHEN RECORD_TYPE='FH'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
FILE_TYPE ,
UPLOAD_TYPE ,
FILE_CREATION_DATE DATE(  "DDMMYYYY",
FILE_SEQUENCE_NUMBER ,
UPLOADER_TYPE ,
TAN_OF_DEDUCTOR ,
TOTAL_NO_OF_BATCHES ,
RECORD_HASH ,
FVU_VERSION ,
FILE_HASH ,
SAM_VERSION ,
SAM_HASH ,
SCM_VERSION ,
SCM_HASH
)
INTO TABLE LAXMAN_ETDS_BH
WHEN RECORD_TYPE='BH'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
BATCH_NUMBER ,
COUNT_OF_CHLNRECORDS ,
FORM_NUMBER ,
TRANSACTION_TYPE ,
BATCH_UPDATION_INDICATOR ,
ORIGINAL_RRR_NO ,
PREVIOUS_RRR_NUMBER ,
RRR_NUMBER ,
RRR_DATE ,
LAST_TAN_DEDUCTOR ,
TAN_OF_DEDUCTOR ,
FILLER1 ,
PAN_OF_DEDUCTOR ,
ASSESSMENT_YR ,
FINANCIAL_YR ,
PERIOD ,
NAME_OF_DEDUCTOR ,
DEDUCTOR_BRANCH ,
DEDUCTOR_ADDRESS1 ,
DEDUCTOR_ADDRESS2 ,
DEDUCTOR_ADDRESS3 ,
DEDUCTOR_ADDRESS4 ,
DEDUCTOR_ADDRESS5 ,
DEDUCTOR_STATE ,
DEDUCTOR_PINCODE ,
DEDUCTOR_EMAILID ,
DEDUCTOR_STD ,
DEDUCTOR_PH_NO ,
ADDRESS_CAHNGE_SINCE_LAST_RET ,
DEDUCTOR_TYPE ,
PERSON_RESPONSIBLE_DEDUCTION ,
DESIGNATION ,
RESP_PERSON_ADDRESS1 ,
RESP_PERSON_ADDRESS2 ,
RESP_PERSON_ADDRESS3 ,
RESP_PERSON_ADDRESS4 ,
RESP_PERSON_ADDRESS5 ,
RESP_PERSON_STATE ,
RESP_PERSON_PIN ,
RESP_PERSON_EMAIL ,
REMARK ,
RESP_PERSON_STD_CODE ,
RESP_PERSON_PHONE_NO ,
CHANGE_ADDR_SINCE_LAST_RETURN ,
BATCH_TOTAL_DEPOSIT_AMT ,
TDSCIRCLE ,
COUNT_SALARY_DET_RECORDS ,
BATCH_TOTAL_GROSS_INCOME ,
AO_APPROVAL ,
AO_APPROVAL_NUMBER ,
RECORD_HASH
)
INTO TABLE LAXMAN_ETDS_CD
WHEN RECORD_TYPE='CD'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NUMBER ,
RECORD_TYPE ,
BATCH_NUMBER ,
CHLN_DET_RECORD_NO ,
COUNT_DEDUCTEE ,
NIL_CHLN_INDICATOR ,
CHLN_UPDATION_INDICATOR ,
FILLER_2 ,
FILLER_3 ,
FILLER_4 ,
LAST_BANK_CHLN_NO ,
BANK_CHALLAN_NO ,
LAST_TRANSFER_VCH_NO ,
TRANSFER_VCH_NO ,
LAST_BANK_BRANCH_CODE ,
BANK_BRANCH_CODE ,
LAST_DATE_BANK_CHLN_NO ,
DATE_BANK_CHLN_NO DATE( "DDMMYYYY",
FILLER_5 ,
FILLER_6 ,
SECTION ,
OLTAS_TDS_INCOME_TAX ,
OLTAS_TDS_SURCHARGE ,
OLTAS_TDS_CESS ,
OLTAS_TDS_INTEREST_AMT ,
OLTAS_TDS_OTHERS ,
TOTAL_DEPOSIT_AMT_CHLN ,
LAST_TOTAL_DEP_AMT ,
TOT_TAX_DEP_AMT ,
TDS_INCOME_TAX ,
TDS_SURCHARGE ,
TDS_CESS ,
SUM_TOT_IT_DEDUCTED_SOURCE ,
TDS_TCS_INTEREST_AMT ,
TDS_TCS_OTHERS_AMT ,
CHEQUE_DD_NO ,
BOOK_ENTRY_CASH ,
REMARKS ,
RECORD_HASH
)
INTO TABLE LAXMAN_ETDS_DD
WHEN RECORD_TYPE='DD'
FIELDS TERMINATED BY '^' TRAILING NULLCOLS
(
LINE_NO ,
RECORD_TYPE ,
BATCH_NUMBER ,
CHLN_DET_RECORD ,
DEDUCTEE_DETAIL_RECORD_NO ,
MODE_ETDS ,
EMPLOYEE_SERIAL_NO ,
DEDUCTEE_CODE ,
LAST_PARTY_PAN ,
DEDUCTEE_PAN ,
LAST_PARTY_PAN_REF_NO ,
PAN_REF_NO ,
NAME_DEDUCTEE ,
TDS_IT_FOR_PERIOD ,
TDS_SURCHARGE ,
TDS_TCS_CESS ,
TOT_IT_DEDUC_SOURCE ,
LAST_TOT_IT_DEDUCT_SOURCE ,
TOT_TAX_DEPOSITED ,
LAST_TOT_TAX_DEPOSITED ,
TOT_PURCHASE ,
AMT_PAYMENT_CREDIT ,
DATE_AMT_PAID DATE( "DDMMYYYY",
DATE_TAX_DEDUCTED DATE( "DDMMYYYY",
DATE_DEPOSIT ,
RATE_TAX_DED_COLL ,
GROSSING_UP_IND ,
BOOK_ENTRY ,
DATE_TAX_DEDUCTION ,
REMARKS_1 ,
REMARKS_2 ,
REMARKS_3 ,
RECORD_HASH
)
The parfile is
control=loader.ctl
log=loader_log.log
bad=loader.bad
discard=discard.log
skip=0
rows=1
bindsize=100000
readsize=1000000
direct=N
discardmax=100000000
errors=1000000
-
Attachment: ABCD.txt
(Size: 9.53KB, Downloaded 1290 times)
|
|
|
|
| Re: Loading data into multiple tables [message #201977 is a reply to message #201888] |
Tue, 07 November 2006 12:12  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
There is a pecuiliarity when you use multiple when clauses with a delimited data file. In order to get Oracle to read from the correct position, you need to add the starting position to the first column. So, you would need to change each "line_number," to "line_number position(1),".
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 16:23:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
