| Insert data into multiple tables from a single datafile [message #243119] |
Wed, 06 June 2007 02:48  |
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: [Urgent] Insert data into multiple table from a single datafile [message #243337 is a reply to message #243164] |
Wed, 06 June 2007 23:56  |
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'
)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
