OraFAQ Forum: Server Utilities » SQL Loader to load single row into multiple rows

Home » RDBMS Server » Server Utilities » SQL Loader to load single row into multiple rows (Oracle 10.2G)

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

SQL Loader to load single row into multiple rows [message #400498]

Tue, 28 April 2009 08:40

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.

Report message to a moderator

Re: SQL Loader to load single row into multiple rows [message #400504 is a reply to message #400498]

Tue, 28 April 2009 08:58

cookiemonster
Messages: 13973
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.

Report message to a moderator

Re: SQL Loader to load single row into multiple rows [message #400823 is a reply to message #400498]

Wed, 29 April 2009 14:38

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator