Home » RDBMS Server » Server Utilities » loading data into two tables from single input file
loading data into two tables from single input file [message #199162] Fri, 20 October 2006 12:51 Go to next message
roopla
Messages: 52
Registered: May 2006
Member

Is it possible to load into two different tables using sql loader.
I have a input file which has header record and detail records. I want header record go into different table and details go into other table.

Please advice
Re: loading data into two tables from single input file [message #199163 is a reply to message #199162] Fri, 20 October 2006 13:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In a broad sense, yes you can.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch10.htm#1007219
How are you going to identify and inform sqlldr that a record is 'header' or 'tail'?
Search the forum.
There are many examples around.
Re: loading data into two tables from single input file [message #199164 is a reply to message #199163] Fri, 20 October 2006 13:02 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
Very first and Last records and header and trailer. Is there any command verb which might bypass these two records while loading into table

Thanks
Re: loading data into two tables from single input file [message #199165 is a reply to message #199164] Fri, 20 October 2006 13:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said before, Search the forum before posting. many examples are around.
You can exclude the first and and lart records.
Post a sample datafile along with table defintions and which data should go where (after the load).

[Updated on: Fri, 20 October 2006 13:08]

Report message to a moderator

Re: loading data into two tables from single input file [message #199168 is a reply to message #199165] Fri, 20 October 2006 13:31 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
First and last records should go to controltb table
and rest should go to details table



Detail Table
create table details (rtyp varchar(10),sttpe varchar(10),st varchar(2),stdt varchar(30),stind varchar(2))


control Table

create table controltb (rtyp varchar(10),hdtpe varchar(10),stdate date,stdt varchar(30))





Datafile
00,HDR,09/28/2006,00000000007
66,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
67,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
68,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
69,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
10,TRL,09/28/2006,00000000007
Re: loading data into two tables from single input file [message #199172 is a reply to message #199168] Fri, 20 October 2006 14:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You have to do some scripting.
What is your OS?
In unix it is easily possible
easiest method is to strip away the last and first line into a new file.
So you have two files now. Load them seperately.
Or
You can skip the first record (use skip=1)
You can also skip the last record use load=N-1 as said here
http://www.orafaq.com/forum/t/27004/0/
That will take care loading into detail table.
There is no pure sqlldr method (i am aware of) to skip all records in between header and detail .If you have a special record in header and tail, then you can use a where clause.
Something like
When specialcolumn= magicvalue
load into this table
when specialcolumn= nomagicvalue
load into another table.

Re: loading data into two tables from single input file [message #199173 is a reply to message #199172] Fri, 20 October 2006 14:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
00,HDR,09/28/2006,00000000007
10,TRL,09/28/2006,00000000007

If your datafiles will consistently have HDR and TRL values,
it is easy.
The case study i pointed you just does that.
Re: loading data into two tables from single input file [message #199174 is a reply to message #199173] Fri, 20 October 2006 14:39 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
I use UNIX k-shell. I think the suggestion you gave to create two separate files is good one. Can you please post a quick syntax to write first and last records from input file into other file using awk.

Thanks Again
Re: loading data into two tables from single input file [message #199175 is a reply to message #199174] Fri, 20 October 2006 14:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you never answered my question.
>>If your datafiles will consistently have HDR and TRL values,
You no need any scripting at all.
Re: loading data into two tables from single input file [message #199176 is a reply to message #199175] Fri, 20 October 2006 14:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Anyhow, you asked and awk is an overkill Smile
oracle@mutation#cat sample.data
00,HDR,09/28/2006,00000000007
66,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
67,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
68,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
69,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
10,TRL,09/28/2006,00000000007
oracle@mutation#
oracle@mutation#
oracle@mutation#head -1 sample.data > headtail.data
oracle@mutation#tail -1 sample.data >> headtail.data
oracle@mutation#
oracle@mutation#cat sample.data  | grep -v `head -1 sample.data` | grep -v `tail -1 sample.data` > detail.data
oracle@mutation#
oracle@mutation#cat headtail.data
00,HDR,09/28/2006,00000000007
10,TRL,09/28/2006,00000000007
oracle@mutation#
oracle@mutation#cat detail.data
66,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
67,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
68,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
69,MDVW,VA,VAFBF0,M0049-001-000-000-001,Y
oracle@mutation#


[Updated on: Fri, 20 October 2006 15:00]

Report message to a moderator

Re: loading data into two tables from single input file [message #199177 is a reply to message #199162] Fri, 20 October 2006 15:03 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
yes, datafiles will consistently have HDR and TRL values
Re: loading data into two tables from single input file [message #199178 is a reply to message #199177] Fri, 20 October 2006 15:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
then look into my first response.
The documentation will give a working example.
Re: loading data into two tables from single input file [message #199179 is a reply to message #199162] Fri, 20 October 2006 17:26 Go to previous messageGo to next message
roopla
Messages: 52
Registered: May 2006
Member
Thanks a lot for your help.
One more question for you. I have a field in table number(10,2), but my data file has 0000009920(they are not sending me decimal point 99.20). How can I load this data in right format into the field like 99.20 instead of 9920

Thanks
Re: loading data into two tables from single input file [message #199213 is a reply to message #199179] Sat, 21 October 2006 05:32 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Once again,
Search the forum.
There are many examples around.
This is just an sql functionality.
Previous Topic: execute immediate with Alter user statement
Next Topic: Import dmp file from server to another server
Goto Forum:
  


Current Time: Sun Dec 04 10:48:05 CST 2016

Total time taken to generate the page: 0.07285 seconds