SQL Loader : how to load multiple record into db from same record of input file ?

donadarsh · #1 (**permalink**) 11-16-11, 08:49

Hi All,

I am facing tough time with SQL loader.

I have a requirement to load from a row where same set of attribute can be repeated up to 50 times, I need to load all these data as separate records.

Input file data

Code:

A1|234|345|2334|23444|A2|234|345|2334|23444|A3|234|345|2334|23444|A4|234|345|2334|23444|A5|234|345|2334|23444|A6|234|345|2334|23444|

Record in DB after loading it

Code:

A1|234|345|2334|23444|
A2|234|345|2334|23444|
A3|234|345|2334|23444|
A4|234|345|2334|23444|
A5|234|345|2334|23444|
A6|234|345|2334|23444|

I thought it of doing it in loop but SQL loader doesn't support loop or else I need to write 50 When clause.

Note: The above input line comes after some fixed columns that is getting loaded into another table. These records will be loaded into separate table if it exists in input file record.

Any help?

anacedent · #2 (**permalink**) 11-16-11, 09:12

EXTERNAL TABLE might be part of the solution.

Littlefoot · #3 (**permalink**) 11-16-11, 16:11

Here's "how to" that uses SQL*Loader.

Note use of FILLER fields; the first INSERT INTO loads first set; the second one uses FILLER for the first 5 fields and loads fields 6 - 10. The third one uses FILLER for fields 1 - 10 and loads the next 5 fields, etc. In order to keep the control file as short as possible but, still, show what I mean, I applied the technique for the first 3 data sets (A1, A2, A3 and the second record I made up based on your data - A7, A8 and A9).

Code:

load data
infile *
replace
into table test
  fields terminated by "|"
(col1,
 col2,
 col3,
 col4,
 col5
)
into table test
  fields terminated by "|"
(fi1 filler position(1),
 fi2 filler,
 fi3 filler,
 fi4 filler,
 fi5 filler,
 col1,
 col2,
 col3, 
 col4,
 col5
)
into table test
  fields terminated by "|"
(fi1 filler position(1),
 fi2 filler,
 fi3 filler,
 fi4 filler,
 fi5 filler,
 fi6 filler,
 fi7 filler,
 fi8 filler,
 fi9 filler,
 fi10 filler,
 col1,
 col2,
 col3,
 col4,
 col5
)

begindata
A1|234|345|2334|23444|A2|234|345|2334|23444|A3|234|345|2334|23444|A4|234|345|2334|23444|A5|234|345|2334|23444|A6|234|345|2334|23444|
A7|234|345|2334|23444|A8|234|345|2334|23444|A9|234|345|2334|23444|A14|234|345|2334|23444|A15|234|345|2334|23444|A16|234|345|2334|23444|

Loading session and the result:

Code:

SQL> $sqlldr scott/tiger control=p.ctl log=p.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Stu 16 22:07:52 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL>
SQL>
SQL>
SQL> select * from test;

COL1                       COL2       COL3       COL4       COL5
-------------------- ---------- ---------- ---------- ----------
A1                          234        345       2334      23444
A8                          234        345       2334      23444
A9                          234        345       2334      23444
A2                          234        345       2334      23444
A3                          234        345       2334      23444
A7                          234        345       2334      23444

6 rows selected.

SQL>

Internet Services

Web Development

Digital Marketing

Consumer Tech