Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: SQL Loader selectively load fields

    Hi,

    Is there a way to selectively load fields into different tables using SQL Loader. My input is a tab-delimited file.

    example:
    name us? visa visaNumber Course
    "person1" "us" "" "" "math"
    "person2" "foreign" "F1" "1234" "economics"

    here, depending if the person requires a visa, i need it to populate a SEPARATE visa details table. If they do not require a visa, i need to carry on at the next field into the course table.

    here is what i have:

    ....
    INTO TABLE VISAS
    WHEN VISA_TYPE != ''
    FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'
    (
    VISA_TYPE,

    VISA_NUMBER
    )

    but then when i get to the courses table, i am at the wrong position in the file.

    Any ideas?

    Thanks for your help

  2. #2
    Join Date
    Aug 2003
    Posts
    41
    There could be ways of doing this in SQL loader. But I would stick with what i know best than to play around with different options of Sql Loader. I would load a temporary table with all the fields as it is from the file and write SQL statements to populate my tables from the temporary table.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    use decode function for conditioning
    LOAD DATA
    INFILE 'mail_orders.txt'
    BADFILE 'bad_orders.txt'
    APPEND
    INTO TABLE mailing_list
    FIELDS TERMINATED BY ","
    ( addr,
    city,
    state,
    zipcode,
    mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
    mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
    mailing_state
    )

    load data into multiple tables at once?
    Look at the following control file:
    LOAD DATA
    INFILE *
    REPLACE
    INTO TABLE emp
    WHEN empno != ' '
    ( empno POSITION(1:4) INTEGER EXTERNAL,
    ename POSITION(6:15) CHAR,
    deptno POSITION(17:18) CHAR,
    mgr POSITION(20:23) INTEGER EXTERNAL
    )
    INTO TABLE proj
    WHEN projno != ' '
    ( projno POSITION(25:27) INTEGER EXTERNAL,
    empno POSITION(1:4) INTEGER EXTERNAL
    )
    see attach file
    Attached Files Attached Files

Posting Permissions

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