OraFAQ Forum: Server Utilities » How to insert the data from data file into multiple tables using the delimited (like comma)

Home » RDBMS Server » Server Utilities » How to insert the data from data file into multiple tables using the delimited (like comma) (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit)

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

How to insert the data from data file into multiple tables using the delimited (like comma) [message #423920]

Tue, 29 September 2009 04:57

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

How to insert the data from data file into multiple tables using the delimited but not position wise?

Data File


001,tata,get,220310
001,tata,get,220311
001,tata,get,220312
001,tata,get,220313
002,manoj,get,220310
002,manoj,get,220311
002,manoj,get,220312
002,manoj,get,220313
003,game,get,220310
003,game,get,220311
003,game,get,220312
003,game,get,220313

Note:First value in the data file is table code or can be table name.

Please suggest.

Report message to a moderator

Re: How to insert the data from data file into multiple tables using the delimited (like comma) [message #423922 is a reply to message #423920]

Tue, 29 September 2009 05:07

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

use an external table then write some PL/SQL to get the data in the right tables.

Report message to a moderator

Re: How to insert the data from data file into multiple tables using the delimited (like comma) [message #423924 is a reply to message #423922]

Tue, 29 September 2009 05:12

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

Each time the file name will be changed.Is there any option in External table, where it will accept the dynamic file name in the defintion of external table.

where file naming convention can be like :
  xxx_ddmmhh24miss.dat

Pls. suggest.

Report message to a moderator

Re: How to insert the data from data file into multiple tables using the delimited (like comma) [message #423927 is a reply to message #423924]

Tue, 29 September 2009 05:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ALTER TABLE ... LOCATION ...
No need of PL/SQL just use an INSERT ALL.

Regards
Michel

Report message to a moderator

Previous Topic:	Step-by-Step Recovering dump file using IMP
Next Topic:	Merge two identical databases/schemas.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:34:38 CDT 2024