Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

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

    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?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXTERNAL TABLE might be part of the solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •