Home » RDBMS Server » Server Utilities » SQL Loader to load single row into multiple rows (Oracle 10.2G)
icon5.gif  SQL Loader to load single row into multiple rows [message #400498] Tue, 28 April 2009 08:40 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,

Is there a way to load a single row of date into multiple rows using SQL Loader?

Example:
Source file source.csv
# CountryID,ClientID,Field1,,Field2,
1,1,1001,2001,3001,4001
1,2,1002,2002,3002,4002


Desired content of the destination DB table:
# CountryID,ClientID,FieldName,Val1,Val2
1,1,Field1,1001,2001
1,1,Field2,3001,4001
1,2,Field1,1002,2002
1,2,Field2,3002,4002


The control file for SQL Loader would be created dynamically.
Input .csv file can have variable number of columns (leading columns and 2*n data columns).

I have tried the following:
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'source.csv'
APPEND INTO "MY_TABLE"
FIELDS TERMINATED BY ','
(
 CountryID BOUNDFILLER,
 ClientID  BOUNDFILLER,
 CountryID ":CountryID",
 ClientID  ":ClientID",
 FieldName constant 'Field1',
 Val1      ,
 Val2      
)
(
 CountryID ":CountryID",
 ClientID  ":ClientID",
 FieldName constant 'Field2',
 Val1Filler FILLER,
 Val2Filler FILLER,
 Val1      ,
 Val2      
)

But the above control file gives me
SQL*Loader-404: Column CountryID present more than once in "MY_TABLE"'s INTO TABLE block.
Re: SQL Loader to load single row into multiple rows [message #400504 is a reply to message #400498] Tue, 28 April 2009 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't know if you can do it in sqlloader - I kind of doubt it.

But if you use an external table instead you can use standard pivoting techniques to get the result you want.
Re: SQL Loader to load single row into multiple rows [message #400823 is a reply to message #400498] Wed, 29 April 2009 14:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
-- source.csv:
# CountryID,ClientID,Field1,,Field2,
1,1,1001,2001,3001,4001
1,2,1002,2002,3002,4002


-- test.ctl:
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'source.csv'
APPEND
INTO TABLE "MY_TABLE"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
  (CountryID, ClientID,
   FieldName constant 'Field1',
   Val1, Val2)
INTO TABLE "MY_TABLE"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
  (CountryID POSITION (1), ClientID,
   FieldName constant 'Field2',
   filler1 FILLER, filler2 FILLER,
   Val1, Val2)


SCOTT@orcl_11g> CREATE TABLE my_table
  2    (countryID  NUMBER,
  3  	ClientID   NUMBER,
  4  	FieldName  VARCHAR2 (30),
  5  	Val1	   NUMBER,
  6  	Val2	   NUMBER)
  7  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM my_table ORDER BY countryid, clientid, fieldname
  2  /

 COUNTRYID   CLIENTID FIELDNAME                            VAL1       VAL2
---------- ---------- ------------------------------ ---------- ----------
         1          1 Field1                               1001       2001
         1          1 Field2                               3001       4001
         1          2 Field1                               1002       2002
         1          2 Field2                               3002       4002

SCOTT@orcl_11g>

Re: SQL Loader to load single row into multiple rows [message #400832 is a reply to message #400823] Wed, 29 April 2009 15:33 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I know I'm not the only one who is thinking: NICE!
Or more accurately, EVERYONE is thinking the same thing.
icon14.gif  Re: SQL Loader to load single row into multiple rows [message #400926 is a reply to message #400823] Thu, 30 April 2009 07:16 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks Barbara!
Previous Topic: ERROR 350
Next Topic: sql* loader (merged)
Goto Forum:
  


Current Time: Fri Dec 09 00:02:20 CST 2016

Total time taken to generate the page: 0.09434 seconds