Printable View
hi,
I have a single datafile containing data for multiple tables. Data for each table is identified by a different record type
egs
(datafile.txt)
A 1001name1 age1
B 20 deptname
In the above datafile, I need to take A.... into emp table
and
B .... into dept table.
What changes should I make in the control file to write the data into multiple tables
Please help me
Badrinath
I am writing a controlfile for you. Do you want
1001name1 age1 into one column of your oracle table or several columns?
Same about 20 deptname - two columns?
Anyway, if you need to load it into defferent columns of your multiple table, here is an example:
controlfile:
LOAD DATA
INFILE acontr.dat
REPLACE
INTO TABLE junk10
(col1 position(1:4),
col2 position(5:9),
col3 position(10:15))
INTO TABLE junk11
(col1 position(16:17),
col2 position(18:26))
Tables description:
SVRMGR> desc junk10
Column Name Null? Type
------------------------------ -------- ----
COL1 NUMBER
COL2 VARCHAR2(40)
COL3 VARCHAR2(40)
SVRMGR> desc junk11
Column Name Null? Type
------------------------------ -------- ----
COL1 NUMBER
COL2 VARCHAR2(40))
datafile called acontr.dat:
1001name1 age1 20 deptname
I just counted pistition in this line: position 1 through 4 is 1001 and I put it in col1 of table junk10. Position 5 through 9 is name1 and I put it in col2 of same tables. Sequentially you count all your positions and put them in columns you want.
So after load you get:
SVRMGR> select * from junk10;
COL1 COL2 COL3
---------- ---------------------------------------- ----------------------------------------
1001 name1 age1
1 row selected.
SVRMGR> select * from junk11;
COL1 COL2
---------- ----------------------------------------
20 deptname
1 row selected.
Main thing - you really need to know what part of your datafile goes into what part of oracle tables :-)