Home » RDBMS Server » Server Utilities » data into Multiple tables using SQL Loader
| data into Multiple tables using SQL Loader [message #74208] |
Fri, 08 October 2004 09:49  |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
I have a text file that contains data as below:
@,9343,W,37,377701,1,4
$,8,43
$,8,45
@,9343,W,37,377701,1,4
$,8,45
$,8,47
@,9343,W,37,377701,5,2
$,11,42
There is a marker of filler field for every record and I have Control
file that is supposed to load the record based on the marker for
the record, into the appropriate table.
The record with @ marker is the parent table record and $ is the
child table record.
Now I am using a new database sequence value for every insert into
the parent table and I use the current seq value for the
corresponding insert into the child table. But for some reason,
the inserted seq values into the child table don't seem to match
exactly seq value in the corresponding parent even though I am
requesting for a current value. Can you help me out as to how
SQL Loader is executing this script and how I can correct it so
that it inserts a parent record and then uses that seq value
generated to insert into the child table.
The results after the SQL Loader runs look like these:
TBL_PARENT
Seqcol col1 col2 col3........
-----------------------------
1 value value value....
2 value value value......
TBL_CHILD
Seqcol col1 col2
-----------------------------
2 value value
2 value value
The insert into the child should have had 1 instead of 2
My script file looks like this:
LOAD DATA
INFILE 'test.dat'
REPLACE
INTO TABLE TBL_PARENT
WHEN (REC_MARKER = '@')
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
REC_MARKER FILLER POSITION(1) CHAR TERMINATED BY ',',
COL1 INTEGER EXTERNAL,
COL2 CHAR,
COL3 CHAR,
COL4 CHAR,
COL5 CHAR,
COL6 CHAR,
R_SEQ_ID "DB_SEQ.NEXTVAL",
LST_USER_ID "User",
LST_UPDT_DT SYSDATE
)
INTO TABLE TBL_CHILD
WHEN (REC_MARKER = '$')
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
REC_MARKER FILLER POSITION(1) CHAR TERMINATED BY ',',
COL1 INTEGER EXTERNAL,
COL2 INTEGER EXTERNAL,
R_SEQ_ID "DB_SEQ.CURRVAL",
LST_USER_ID "User",
LST_UPDT_DT SYSDATE
)
Thanks in advance.
|
|
|
|
| Re: data into Multiple tables using SQL Loader [message #74211 is a reply to message #74208] |
Fri, 08 October 2004 21:52  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
SQL*Loader processes all rows for the first "into table" before processing the rows for the second "into table", so all rows in the second table get the last sequence from the first table. One solution is to use "options (rows=1)" at the top of your control file, so that it processes one rows for both tables, then proceeds to the next row. This will, however, slow your load down considerably. Another option is to load the data into a staging table, then distribute it from there.
|
|
|
|
Goto Forum:
Current Time: Wed Dec 25 08:05:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
