This discussion is archived
14 Replies Latest reply: Mar 25, 2013 8:05 PM by TSharma-Oracle RSS

Single Source Data, into 2 Tables

617165 Newbie
Currently Being Moderated
Hello Everyone,

Little Loading problem: -

CREATE TABLE: -
 
CREATE TABLE TestTab1
(
COL1 VARCHAR2(10),
COL2 VARCHAR2(10), 
COL3 VARCHAR2(10),
COL4 VARCHAR2(10), 
COL5 VARCHAR2(10),
COL6 VARCHAR2(10),
COL7 VARCHAR2(10),
SEQNO NUMBER(10)
);

CREATE TABLE TestTab2
(
COL5 VARCHAR2(10),
COL6 VARCHAR2(10),
COL7 VARCHAR2(10),
COL8 VARCHAR2(10),
SEQNO NUMBER(10)
);
DATA stored a notepad file (Test_datafile.txt) on local drive:-
 
1,2,3,4,5,6,7,8
9,10,11,12,13,14,15,16
17,18,19,20,21,22,23,24
25,26,27,28,29,30,31,32
My issue is, I need to split the data into two seperate tables but hold the same unique seqno between the tables so I can link them further down the line. I cant use a Fixed Width loadscript as my actual working example holds in excess off 1000 columns. So I need the beginning of the row to go into one table and the end to go into a 2nd table (this said, it could be the middle of the datafile that i need shifting into 2nd table). Is there a way to tell sqlldr to put columns 1-7 in a table and 6-8 into another without using a fixed width.

Current progress doesnt work: -
 
--Log on to SQLLDR

LOAD DATA
INFILE 'Test_datafile.txt' BADFILE 'Badfile.txt'

APPEND 
INTO TABLE TestTab1
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
SEQNO     sequence (max,1)
)
 INTO TABLE TestTab2
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
COL6,
COL7,
COL8,
SEQNO sequence (max,1)     
)
Any ideas guys?
  • 1. Re: Single Source Data, into 2 Tables
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    You should do it with an external table as they are much more flexible. You could do a multi table insert direct from that. If you can't do that for any reason then just load into intermediate table and multi table insert from that?

    Cheers,
    Harry
  • 2. Re: Single Source Data, into 2 Tables
    617165 Newbie
    Currently Being Moderated
    What's the performance like on external tables? I have 1000+ fields and millions of rows. Also are you saying what I need can't be done in sqlldr?

    Thanks
  • 3. Re: Single Source Data, into 2 Tables
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions. I have not tried this myself, but there is an example in the docs -

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#i1005798

    I also am not too sure about populating the same sequence number into two separate tables.

    HTH
    Srini
  • 4. Re: Single Source Data, into 2 Tables
    617165 Newbie
    Currently Being Moderated
    11g and win2008 64bit

    Thanks
  • 5. Re: Single Source Data, into 2 Tables
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    External tables are as fast as sqlldr it's the same technology. You can definitely do it that way. It may be possible directly in sqlldr but a way of doing it doesn't spring to mind immediately perhaps someone else can find the right syntax.

    Cheers,
    Harry
  • 6. Re: Single Source Data, into 2 Tables
    damorgan Oracle ACE Director
    Currently Being Moderated
    No need for anything fancy here ... Oracle has an insert statement specifically crafted for this purpose.

    Demo here:
    http://www.morganslibrary.org/reference/insert.html

    Look up the INSERT WHEN statement.

    While on the page also look at INSERT ALL and INSERT FIRST statements.
  • 7. Re: Single Source Data, into 2 Tables
    617165 Newbie
    Currently Being Moderated
    Sorry for my ignorance, but I've not seen an "insert when" in sqlldr without using fixed positions, could you help with an example based on the above scenario?
  • 8. Re: Single Source Data, into 2 Tables
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Give this an example a try - it worked fine for me:

    Create some needed objects
    create directory demo as '/home/oracle';
    create sequence test;
    create external table (just a reference to the sqlldr data file essentially)
    
    CREATE TABLE "HARRY"
    (
      "COL1" VARCHAR2(10),
      "COL2" VARCHAR2(10),
      "COL3" VARCHAR2(10),
      "COL4" VARCHAR2(10),
      "COL5" VARCHAR2(10),
      "COL6" VARCHAR2(10),
      "COL7" VARCHAR2(10),
      "COL8" VARCHAR2(10)
    )
    ORGANIZATION external
    (
      TYPE oracle_loader
      DEFAULT DIRECTORY DEMO
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        BADFILE 'DEMO':'a.bad'
        LOGFILE 'a.log_xt'
        READSIZE 1048576
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL FIELDS
        (
          "COL1" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL2" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL3" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL4" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL5" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL6" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL7" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
          "COL8" CHAR(255)
            TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        )
      )
      location
      (
        'a.txt'
      )
    )REJECT LIMIT UNLIMITED;
    {code}
    
    Now do multi table insert
    
    {code}
    
    insert all into testtab1 values(COL1,COL2,COL3,COL4,COL5,COL6,COL7,test.nextval)
    into testtab2 values (COL5,COL6,COL7,COL8,test.nextval)
    select * from harry;
    
    {code}
    
    See if that works for you?
    
    Cheers,
    Harry                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 9. Re: Single Source Data, into 2 Tables
    617165 Newbie
    Currently Being Moderated
    Hello Richard, my real working example holds more than 1000 fields, so this wouldn't work.

    I need a direct feed into 2 tables without using an interim stage.

    Thanks
  • 10. Re: Single Source Data, into 2 Tables
    damorgan Oracle ACE Director
    Currently Being Moderated
    Not SQL*Loader ... an external table pointing to your file. Turn the file into an external table and use INSERT WHEN to load your internal tables.
  • 11. Re: Single Source Data, into 2 Tables
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    This works for just sqlldr dorectly - but it will need some typing..... i don;t think there is a way of doing what you want without a bit of effort unfortunately.....
    LOAD DATA
    INFILE 'a.txt' BADFILE 'a.bad'
    
    APPEND
    INTO TABLE TestTab1
    FIELDS TERMINATED BY "," optionally enclosed by '"'
    trailing nullcols
    (
    COL1,
    COL2,
    COL3,
    COL4,
    COL5,
    COL6,
    SEQNO   sequence (max,1)
    )
     INTO TABLE TestTab2
    FIELDS TERMINATED BY "," optionally enclosed by '"'
    trailing nullcols
    (
    COL1 filler position(1) ,
    COL2 filler ,
    COL3 filler ,
    COL4 filler ,
    COL5 filler ,
    COL6 filler ,
    COL7,
    COL8,
    SEQNO sequence (max,1)
    )
    The position keyword works but cannot be used directly for delimited data - i.e. position(5) is not the 5th delimited value it's the fifth character in the file. This example works just because it resets to the first point in the file.

    See what you think - but I don't think you'll like it....

    Cheers,
    Harry
  • 12. Re: Single Source Data, into 2 Tables
    TSharma-Oracle Guru
    Currently Being Moderated
    Have you tried *"nextval" and "currval"* of sequence. I have not tried this but I think this should work without changing much. You can check the right syntax. In this case, it is using next sequence value for MAster table and same value for child table.

    LOAD DATA
    INFILE 'Test_datafile.txt' BADFILE 'Badfile.txt'

    APPEND
    INTO TABLE TestTab1
    FIELDS TERMINATED BY "," optionally enclosed by '"'
    trailing nullcols
    (
    COL1,
    COL2,
    COL3,
    COL4,
    COL5,
    COL6,
    SEQNO     *sequence_name.nextval*
    )
    INTO TABLE TestTab2
    FIELDS TERMINATED BY "," optionally enclosed by '"'
    trailing nullcols
    (
    COL6,
    COL7,
    COL8,
    SEQNO sequence_name.currval     
    )
  • 13. Re: Single Source Data, into 2 Tables
    617165 Newbie
    Currently Being Moderated
    Thanks Harry - how simple, why didn't I think of that. Yes lots of coding but hey, it works.

    Thanks to everyone for your comments much appreciated.
  • 14. Re: Single Source Data, into 2 Tables
    TSharma-Oracle Guru
    Currently Being Moderated
    Try above suggested reply. If that's work, you won't need to do much changes in your code.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points