Home » RDBMS Server » Server Utilities » Loading into Multiple Tabels
| Loading into Multiple Tabels [message #73397] |
Thu, 15 April 2004 05:00  |
Bharath Kumar V
Messages: 4
Registered: April 2004
|
Junior Member |
|
|
Hi,
I have to load data from an input file into 3 tables based on a value in field,
Example Data in input file:
000,abc,..... (Header)
111,xyz,.....(Data)
999,efg,.....(Trailer)
000,xxx,.....(Header)
111,ssss,.....(Data)
999,ggg,.....(Trailer)
.
.
.
All the data with field values 000 ( or the header records) should be inserted into table 1, field value 111 ( or the data records) should be inserted to table 2 and data with values 999 ( or the trailer records) should go into table 3.
Note: 1. My requirement is, For each of the header, data & trailer pair I should assing a unique sequence number.
2. Trigger is written on table1 to increment the sequence no. And the data and the trailer records use the same sequence number for inserting.
3. I have written a control file :
load data
into table table1
append
when (1) = '0000'
(
. . .
)
into table table2
append
when (1) = '9999'
(
. . .
)
into table table3
append
when (1) <> '9999' AND (1) <> '0000'
(
. . .
)
but this does not give the sequence nos properly. All the header records are getting inserted with the incremental sequence nos, and the data and the trailer records are getting inserted with the last seq. no in the header table.
Can some one help me out.
Regds,
Bharath
|
|
|
|
| Re: Loading into Multiple Tabels [message #73405 is a reply to message #73397] |
Sat, 17 April 2004 01:46  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you can get your data in your input file in fixed columns, instead of delimited, there is another option available. You could drop or disable your triggers and use the SQL*Loader sequence. Please see the example below.
-- contents of test.dat:
000,abc,
111,xyz,
999,efg,
000,xxx,
111,ssss,
999,ggg,
scott@ORA92> -- tables:
scott@ORA92> DESC table1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 VARCHAR2(3)
COL2 VARCHAR2(3)
SEQ NUMBER
scott@ORA92> DESC table2
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 VARCHAR2(3)
COL2 VARCHAR2(3)
SEQ NUMBER
scott@ORA92> DESC table3
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 VARCHAR2(3)
COL2 VARCHAR2(3)
SEQ NUMBER
-- test.ctl:
load data
infile 'test.dat'
into table table1
replace
when col1 = '000'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq SEQUENCE)
into table table2
replace
when col1 = '999'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq SEQUENCE)
into table table3
replace
when col1 <> '999' AND col1 <> '000'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq SEQUENCE)
scott@ORA92> -- load data:
scott@ORA92> host sqlldr scott/tiger control=test.ctl log=test.log
scott@ORA92> -- results:
scott@ORA92> SELECT * FROM table1
2 /
COL COL SEQ
--- --- ----------
000 abc 1
000 xxx 2
scott@ORA92> SELECT * FROM table2
2 /
COL COL SEQ
--- --- ----------
999 efg 1
999 ggg 2
scott@ORA92> SELECT * FROM table3
2 /
COL COL SEQ
--- --- ----------
111 xyz 1
111 sss 2
|
|
|
|
| Re: Loading into Multiple Tabels [message #73406 is a reply to message #73405] |
Sat, 17 April 2004 02:02 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Another method is to drop or disable your trigger and use an Oracle sequence, not a SQL*Loader sequence, but use it directly in SQL*Loader. However, this would require using the rows=1 option, which makes it run very slowly. I have only included the sequence and control file below, since everything else is the same as the first example.
scott@ORA92> -- sequence
scott@ORA92> CREATE SEQUENCE test_seq
2 /
Sequence created.
-- test.ctl:
OPTIONS (ROWS=1)
load data
infile 'test.dat'
into table table1
append
when col1 = '000'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq "test_seq.NEXTVAL")
into table table2
append
when col1 = '999'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq "test_seq.CURRVAL")
into table table3
append
when col1 <> '999' AND col1 <> '000'
trailing nullcols
(col1 POSITION (1:3),
col2 POSITION (5:7),
seq "test_seq.CURRVAL")
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 16:21:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
