sqlldr - how to load multiple csv to multple table

Question

1st question: I am trying to load test1.csv, test2.csv and test3.csv to table1, table2 and table3 respectively using SQLLDR. Please bear with my lack if knowledge in this area, I couldn't get it quite right while defining this in .ctl file, only I can think of is the below code but this is not correct. so my question is how can I make this right or is this possible?

OPTIONS (SKIP=1)
LOAD DATA

INFILE 'test1.csv'
INFILE 'test2.csv'
INFILE 'test2.csv'

TRUNCATE

INTO TABLE table1
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    Col1    "TRIM(:Col1)",
    Col2    "TRIM(:Col2)"
)


INTO TABLE table2
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    Colx    "TRIM(:Colx)",
    Coly    "TRIM(:Coly)"
)


INTO TABLE table3
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    Colp    "TRIM(:Colp)",
    Colq    "TRIM(:Colq)"
)

2nd question: This is an alternative to this first question. Since I couldn't figure it out the first one, what I have done is splitting the loads for each table into multiple .ctl files and calling those all three in a .bat file. This works at least but my question is there a way to process all these 3 .ctl files in a session without mentioning user/password 3 times as below?

sqlldr userid=user/pass@server control=test1.ctl
sqlldr userid=user/pass@server control=test2.ctl
sqlldr userid=user/pass@server control=test3.ctl

Is there already or could there be added an indicator field in the data that could be used to key on which table that data should go to? — Gary_W
I haven't used any column as key to differentiate the data but I can give a try by adding the key. — Chito

Gary_W Gary_W · Accepted Answer · 2016-07-06T17:47:56

If there is a field that can be used which can indicate which table that file's data is intended for, you can do something like this using multiple INFILE satements. Let's say the first field is that indicator and it will not be loaded (define it as a FILLER so sqlldr will ignore it):

...
INTO TABLE table1
WHEN (01) = 'TBL1'
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    rec_skip filler POSITION(1),
    Col1    "TRIM(:Col1)",
    Col2    "TRIM(:Col2)"
)

INTO TABLE table2
WHEN (01) = 'TBL2'
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    rec_skip filler POSITION(1),
    Colx    "TRIM(:Colx)",
    Coly    "TRIM(:Coly)"
)


INTO TABLE table3
WHEN (01) = 'TBL3'
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
    rec_skip filler POSITION(1),
    Colp    "TRIM(:Colp)",
    Colq    "TRIM(:Colq)"
)

So logically, each INTO table WHEN section handles each file. Not that flexible though and arguably harder to maintain. For ease of maintenance you may just want to have a control file for each file? If all files and tables are the same layout you could also load them all into the same staging table (with the indicator) for ease of loading, then split them out programatically into the separate tables afterwards. Pros of that method are faster and easier loading and more control over the splitting into the separate table part of the process. Just some other ideas. I have done each method, depends on the requirements and what you are able to change.

sqlldr - how to load multiple csv to multple table

1 Answers