OraFAQ Forum: Server Utilities » loading data into two tables from single input file

Home » RDBMS Server » Server Utilities » loading data into two tables from single input file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

loading data into two tables from single input file [message #199162]

Fri, 20 October 2006 12:51

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

Report message to a moderator

Re: loading data into two tables from single input file [message #199163 is a reply to message #199162]

Fri, 20 October 2006 13:00

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: loading data into two tables from single input file [message #199164 is a reply to message #199163]

Fri, 20 October 2006 13:02

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

Report message to a moderator

Re: loading data into two tables from single input file [message #199165 is a reply to message #199164]

Fri, 20 October 2006 13:07

Mahesh Rajendran
Messages: 10707
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

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

Report message to a moderator

Re: loading data into two tables from single input file [message #199172 is a reply to message #199168]

Fri, 20 October 2006 14:20

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: loading data into two tables from single input file [message #199173 is a reply to message #199172]

Fri, 20 October 2006 14:22

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: loading data into two tables from single input file [message #199174 is a reply to message #199173]

Fri, 20 October 2006 14:39

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

Report message to a moderator

Re: loading data into two tables from single input file [message #199175 is a reply to message #199174]

Fri, 20 October 2006 14:47

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: loading data into two tables from single input file [message #199176 is a reply to message #199175]

Fri, 20 October 2006 14:59

Mahesh Rajendran
Messages: 10707
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

roopla
Messages: 52
Registered: May 2006

Member

yes, datafiles will consistently have HDR and TRL values

Report message to a moderator

Re: loading data into two tables from single input file [message #199178 is a reply to message #199177]

Fri, 20 October 2006 15:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

then look into my first response.
The documentation will give a working example.

Report message to a moderator

Re: loading data into two tables from single input file [message #199179 is a reply to message #199162]

Fri, 20 October 2006 17:26

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

Report message to a moderator

Re: loading data into two tables from single input file [message #199213 is a reply to message #199179]

Sat, 21 October 2006 05:32

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Previous Topic:	execute immediate with Alter user statement
Next Topic:	Import dmp file from server to another server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 14:05:49 CDT 2024