Results 1 to 3 of 3

Thread: sqlloader

  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unhappy Unanswered: sqlloader

    Hi all,

    In my system I have to import a file several times. It`s a patch to replicate a table from other system. So in each importation I want to insert only the new records. But to determine 'new records' is a difficult task. In my table I use an internal primary key, but in the external table, there are three fields that identify the record, but they don't have to appear allways all of them.
    I give you an example: I have a car table with the following fields: id, number plate, serial number, name. The id is my primary key, but in the external table they can have one of the others fields, or a combination of them, of all of them. When I decided to insert such a record in my system, I give it a unique id.

    In the import process updates will be no carried out.

    So, when I do the import I have to test the value of the fields to test if the car referred by them is already in the system or not.

    I have one more restriction, two cars can have the same name but different number plates. If there are mistakes I have to write it to another table.

    For example:
    If this is my table:
    number plate - serial number - name
    p1 s1 n1
    p2 n2
    n3
    p4 s4
    s5

    (My internal id is irrelevant for the discussion)

    If this is the input file, that are the actions to take:

    p1 s1 n1 --> nothinig, the record already exits
    s2 n2 --> insert and give a message with two cars with the same name. Really I cannot know if they are the same car or not
    p3 s3 n3 --> insert and give a message.
    s1 n9 --> message error in name
    p6 --> insert
    n7 --> insert
    p1 s2 --> error message (The combination of number plate and serial number is unique, and number plate is unique alone and serial number is unique alone)
    s4 n2 --> error message (There is no updates)


    I'm considering to do that with sqlloader, but I don't know how to put the condition.

    The other option is to write a PL/SQL program.

    Can you help me with the sqlloader condition?

    Thank you, and please excuse my poor english and the so long question

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Load all the data into a temporary table and then write a procedure to do everything you want.

    Why?
    1. It is easier to manipulate data once it is in the oracle environment
    2. Procs are a piece of cake
    3. By the time you figure out half of the sqlloader script to try to do what you want, you could have been finished with the Procedure and would be all set. Saves a lot of time. Sqlloader can be a pain for complicated work if you are not already an expert with it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2003
    Posts
    7

    Wink

    Ok. Thank you for your answer.
    I think that you are right. It's better to have a proc that I or another can understand than something impossible to debug or modify.

Posting Permissions

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