Home » RDBMS Server » Server Utilities » Multiple table insert from single data file (oracle 9i)
Multiple table insert from single data file [message #434572] Wed, 09 December 2009 14:56 Go to next message
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 165 times)
Re: Multiple table insert from single data file [message #434579 is a reply to message #434572] Wed, 09 December 2009 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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>
Re: Multiple table insert from single data file [message #434645 is a reply to message #434579] Thu, 10 December 2009 01:53 Go to previous message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Thanks, its working fine
Previous Topic: Export Test database to remote windows location
Next Topic: Export table to .csv file
Goto Forum:
  


Current Time: Tue Dec 06 00:21:12 CST 2016

Total time taken to generate the page: 0.06716 seconds