Tuesday, 23 April 2013

Inserting data into multiple tables using SQL Loader


-----------------------Table Create Statements---------------
CREATE TABLE DENVER_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE ORLANDO_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE MISC_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

-------------------Control File - MFILES.CTL------------------
LOAD DATA
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'
APPEND

INTO TABLE DENVER_PRJ
WHEN PROJNO = '101'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE ORLANDO_PRJ
WHEN PROJNO = '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE MISC_PRJ
WHEN PROJNO != '101' AND PROJNO != '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

No comments:

Post a Comment

CREATING A SUPPLIER IN R12 (Functional and SUPPLIERS IN TCA)

Who is a Supplier? Let us now understand who is a Supplier with the help of a simple example: We consider us as a business, who sell...