OraFAQ Forum: Server Utilities » Multiple table insert from single data file

Home » RDBMS Server » Server Utilities » Multiple table insert from single data file (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple table insert from single data file [message #434572]

Wed, 09 December 2009 14:56

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

There are five tables and each table is identified by code in data file to load the tables:


Table Name: Table code in datafile
Test1     :       1 
Test2     :       2
Test3     :       3
Test10    :       10
Test100   :       100

There are 5 tables:

CREATE TABLE Test1 (
T1F1      NUMBER(4),
T1F2   NUMBER(3)); 

CREATE TABLE Test2 (
T2F1  VARCHAR2(20),
T2F2 VARCHAR2(20));


CREATE TABLE Test3 (
T3F1  VARCHAR2(3),
T3F2   NUMBER(5),
T3F3 NUMBER(2));


CREATE TABLE Test10 (
T1F1      NUMBER(4),
T1F2   NUMBER(3)); 

CREATE TABLE Test100 (
T2F1  VARCHAR2(20),
T2F2 VARCHAR2(20));

Data File 'mtest.asc':

1|10|10
1|20|10
2|A|B
2|B|C
3|A|10|10
3|B|10|10
10|10|10
10|20|10
100|A|B
100|B|C

Control File:

load data
infile 'c:\mtest.asc'
APPEND
INTO TABLE TEST1 
   WHEN recid = '1' 
FIELDS TERMINATED BY '|' 
   (recid  FILLER INTEGER EXTERNAL,
    T1F1 INTEGER EXTERNAL,
    T1F2 INTEGER EXTERNAL
    ) 

INTO TABLE TEST2 
   WHEN recid = '2' 
FIELDS TERMINATED BY '|' 
   (recid  FILLER POSITION (1) INTEGER EXTERNAL,
    T2F1 CHAR,
    T2F2 CHAR)

INTO TABLE TEST3
   WHEN recid = '3' 
FIELDS TERMINATED BY '|' 
   (recid  FILLER POSITION (1) INTEGER EXTERNAL,
    T3F1 CHAR, 
    T3F2 INTEGER EXTERNAL, 
    T3F3 INTEGER EXTERNAL) 
    
INTO TABLE TEST10 
   WHEN recid = '10' 
FIELDS TERMINATED BY '|' 
  (recid  FILLER POSITION (1:2) CHAR,
    T1F1 INTEGER EXTERNAL,
    T1F2 INTEGER EXTERNAL
    ) 

INTO TABLE TEST100 
   WHEN recid = '100' 
FIELDS TERMINATED BY '|' 
   (recid  FILLER POSITION (1:3) CHAR,
    T2F1 CHAR,
    T2F2 CHAR)

Q: Data are loading for Test1,Test2,Test3 but it is not loading the data for test10 & test100. I have attached the log file for the same.

Please suggest.

Attachment: mtest.log
(Size: 4.63KB, Downloaded 1174 times)

Report message to a moderator

Re: Multiple table insert from single data file [message #434579 is a reply to message #434572]

Wed, 09 December 2009 15:42

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Such a control file seems to be working OK

INTO TABLE TEST1 
   WHEN recid = '1' 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
   (recid FILLER POSITION (1) INTEGER EXTERNAL,
    T1F1 INTEGER EXTERNAL,
    T1F2 INTEGER EXTERNAL
    ) 

INTO TABLE TEST2 
   WHEN recid = '2' 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
   (recid FILLER POSITION (1) INTEGER EXTERNAL,
    T2F1 CHAR,
    T2F2 CHAR
    ) 

INTO TABLE TEST3
   WHEN recid = '3' 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
   (recid  FILLER POSITION (1) INTEGER EXTERNAL,
    T3F1 CHAR, 
    T3F2 INTEGER EXTERNAL, 
    T3F3 INTEGER EXTERNAL) 

INTO TABLE TEST10 
   WHEN recid = '10' 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
  (recid  FILLER POSITION (1) INTEGER EXTERNAL,
    T1F1 INTEGER EXTERNAL,
    T1F2 INTEGER EXTERNAL
    ) 

INTO TABLE TEST100 
   WHEN recid = '100' 
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
   (recid FILLER POSITION (1) INTEGER EXTERNAL,
    T2F1 CHAR,
    T2F2 CHAR
    )

Commit point reached - logical record count 9
Commit point reached - logical record count 10

SQL> select * from test1;

      T1F1       T1F2
---------- ----------
        10         10
        20         10

SQL> select * from test2;

T2F1                 T2F2
-------------------- --------------------
A                    B
B                    C

SQL> select * from test3;

T3F       T3F2       T3F3
--- ---------- ----------
A           10         10
B           10         10

SQL> select * from test10;

      T1F1       T1F2
---------- ----------
        10         10
        20         10

SQL> select * from test100;

T2F1                 T2F2
-------------------- --------------------
A                    B
B                    C

SQL>

Report message to a moderator

Re: Multiple table insert from single data file [message #434645 is a reply to message #434579]

Thu, 10 December 2009 01:53

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Thanks, its working fine

Report message to a moderator

Previous Topic:	Export Test database to remote windows location
Next Topic:	Export table to .csv file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 05:09:59 CDT 2024