Home » RDBMS Server » Server Utilities » Insert data into multiple tables from a single datafile
Insert data into multiple tables from a single datafile [message #243119] Wed, 06 June 2007 02:48 Go to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Hi All,

I am new to SQL LOADER .
I am using oracle 10g.

I have to insert data into multiple table from a single datafile which looks like this:


OCMVVOCMCS-VALVVXXXXXX0015952004-01-012003-11-05
OCMWOOCWOLBERWOXXXXXX0014181996-03-272000-12-312000-10-27
OCMZAOC/ORAFRIQUEDUSUDAFRIQUE DU SUDXXXXXX3904632000-06-08
STC404004404-MRW(ALLEMAGNE)EUR2000-01-012000-01-01
STC405006405-MTPLC(GB)GBP1993-01-011993-01-01

The first three letter (i. OCM, STC) identify's which table to insert data into.

column of OCM/STC table :
ID, startdata, enddate etc.

How can I accomplish this?

Re: SQL LOADER [message #243121 is a reply to message #243119] Wed, 06 June 2007 02:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is clearly stated in the documentation (check this also):

use WHEN


[Edit: typo in url-tags)

[Updated on: Wed, 06 June 2007 02:53]

Report message to a moderator

Re: [Urgent] Insert data into multiple table from a single datafile [message #243122 is a reply to message #243119] Wed, 06 June 2007 02:53 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
This should help http://www.orafaq.com/faq/can_one_load_data_from_multiple_files_into_multiple_tables_at_once
Re: [Urgent] Insert data into multiple table from a single datafile [message #243132 is a reply to message #243122] Wed, 06 June 2007 03:56 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Thank u.

I have already looked into the example..
But my problem is.. The table name STC/OCM is not a column in any of the table. Its just acting like a flag.
Can anyone tel me how to handle the issue
Re: [Urgent] Insert data into multiple table from a single datafile [message #243137 is a reply to message #243132] Wed, 06 June 2007 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link Frank posted?
It is said that WHEN supports position speficication.

Regards
Michel
Re: [Urgent] Insert data into multiple table from a single datafile [message #243149 is a reply to message #243137] Wed, 06 June 2007 04:27 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Thank u..

I got the solution
Re: [Urgent] Insert data into multiple table from a single datafile [message #243164 is a reply to message #243149] Wed, 06 June 2007 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And it'd be great you post it.

Regards
Michel
Re: [Urgent] Insert data into multiple table from a single datafile [message #243337 is a reply to message #243164] Wed, 06 June 2007 23:56 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
This is what worked for my requirement

load data
infile 'C:\REF SGF.txt'
into table VPV_DB_PAY
when (1:3) = 'PGM'
(
PAY_C POSITION (4:6) CHAR,
PAY_LIB POSITION (7:42) CHAR,
TER_GEO_C POSITION (43:45) CHAR,
PAY_CRE_DT POSITION (48:57) "to_date(:PAY_CRE_DT, 'YYYY/MM/DD')",
PAY_CRE_USER CONSTANT 'XXX',
PAY_MAJ_DT POSITION (58:67) "to_date(:PAY_MAJ_DT, 'YYYY/MM/DD')",
PAY_MAJ_USER CONSTANT 'XXX'
)
into table VPV_DB_STC
when (1:3) = 'STC'
(
STE_COM_C POSITION(4:6) CHAR,
PAY_C POSITION(7:9) CHAR,
STE_COM_LIB POSITION(10:45) CHAR,
STC_CRE_DT POSITION(49:58) "to_date(:STC_CRE_DT, 'YYYY/MM/DD')",
STC_CRE_USER CONSTANT 'XXX',
STC_MAJ_DT POSITION(59:68) "to_date(:STC_MAJ_DT, 'YYYY/MM/DD')",
STC_MAJ_USER CONSTANT 'XXX'
)

Re: [Urgent] Insert data into multiple table from a single datafile [message #243341 is a reply to message #243337] Thu, 07 June 2007 00:06 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for those who'll need a similar solution.

Regards
Michel
Previous Topic: Question about importing schemas
Next Topic: Importing specific tables in oracle database using unix
Goto Forum:
  


Current Time: Sun Dec 11 06:09:47 CST 2016

Total time taken to generate the page: 0.04854 seconds