Home » RDBMS Server » Server Utilities » problem in loading data into table (10gR2 Linux)
problem in loading data into table [message #519724] Tue, 16 August 2011 09:01 Go to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
Hi all,
i have a table revenue
create table revenue
(
person varchar2(23),
month  varchar2(3),
rev_amt number
)


and i have data in a file like below
Person   Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
   Weber,234,234,123,457,456,287,234,123,678,656,341,567
  Keller,596,276,347,134,743,545,216,456,124,753,346,456
   Meyer,987,345,645,567,834,567,789,234,678,973,456,125
  Holzer,509,154,876,347,146,788,174,986,568,246,324,987
  Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
 Stoller,596,237,976,876,346,567,126,879,125,568,124,753
   Marty,094,234,235,763,054,567,237,457,325,753,577,346
  Studer,784,567,235,753,124,575,864,235,753,864,634,678


i want to load it into the table in the following way.
Person    Month   Revenue
-------------------------
Schnyder    Jan       345
Schnyder    Feb       223
Schnyder    Mar       122
Schnyder    Apr       345
Schnyder    Mai       324
Schnyder    Jun       244
Schnyder    Jul       123
Schnyder    Aug       123
Schnyder    Sep       345
Schnyder    Oct       121
Schnyder    Nov       345
Schnyder    Dez       197
........    ...       ...

Please tell me how to write control file to load this data into the above revenue table.
Thanks in Advance.
Re: problem in loading data into table [message #519731 is a reply to message #519724] Tue, 16 August 2011 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58619
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is easy to do it with an external table and the classical column to row transformation.

SQL> create table revenue
  2  (
  3  person varchar2(23),
  4  month  varchar2(3),
  5  rev_amt number
  6  )
  7
SQL> /

Table created.

SQL> create table ext (
  2    person varchar2(23),
  3    Jan    number,
  4    Feb    number,
  5    Mar    number,
  6    Apr    number,
  7    May    number,
  8    Jun    number,
  9    Jul    number,
 10    Aug    number,
 11    Sep    number,
 12    Oct    number,
 13    Nov    number,
 14    Dec    number)
 15  organization external
 16  (type ORACLE_LOADER default
 17   directory MY_DIR
 18   access parameters (fields terminated by ',')
 19   location ('t.txt'))
 20  /

Table created.

SQL> host type t.txt
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
M³ller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678

SQL> insert into revenue
  2  select person,
  3         decode(line, 1,'Jan', 2,'Feb', 3,'Mar', 4,'Apr', 5,'May', 6,'Jun',
  4                      7,'Jul', 8,'Aug', 9,'Sep', 10,'Oct', 11,'Nov', 12,'Dec'),
  5         decode(line, 1,Jan, 2,Feb, 3,Mar, 4,Apr, 5,May, 6,Jun,
  6                      7,Jul, 8,Aug, 9,Sep, 10,Oct, 11,Nov, 12,Dec)
  7  from ext,
  8       (select level line from dual connect by level <= 12)
  9  /

120 rows created.

SQL> select * from revenue where rownum <= 13;
PERSON                  MON    REV_AMT
----------------------- --- ----------
Schnyder                Jan        345
Weber                   Jan        234
Keller                  Jan        596
Meyer                   Jan        987
Holzer                  Jan        509
M³ller                  Jan        456
Binggeli                Jan        487
Stoller                 Jan        596
Marty                   Jan         94
Studer                  Jan        784
Schnyder                Feb        223
Weber                   Feb        234
Keller                  Feb        276

13 rows selected.

Regards
Michel

[Updated on: Tue, 16 August 2011 10:19]

Report message to a moderator

Re: problem in loading data into table [message #519775 is a reply to message #519731] Tue, 16 August 2011 18:20 Go to previous message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
The following uses SQL*Loader, does not require a staging table, and will work if the data file is on the server or the client.

SCOTT@orcl_11gR2> create table revenue
  2  (
  3  person varchar2(23),
  4  month  varchar2(3),
  5  rev_amt number
  6  )
  7  /

Table created.

SCOTT@orcl_11gR2> host type t.txt
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Mⁿller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678

SCOTT@orcl_11gR2> host type test.ctl
load data
infile t.txt
into table revenue
fields terminated by ','
(person, rev_amt, month constant "Jan")
into table revenue
fields terminated by ','
(person position (1),
Jan filler,
rev_amt, month constant "Feb")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler,
rev_amt, month constant "Mar")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler,
rev_amt, month constant "Apr")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler,
rev_amt, month constant "May")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler,
rev_amt, month constant "Jun")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
rev_amt, month constant "Jul")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler,
rev_amt, month constant "Aug")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler,
rev_amt, month constant "Sep")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler,
rev_amt, month constant "Oct")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler, Oct filler,
rev_amt, month constant "Nov")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler, Oct filler, Nov filler,
rev_amt, month constant "Dec")

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 16 16:16:19 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 10

SCOTT@orcl_11gR2> select * from revenue
  2  /

PERSON                  MON    REV_AMT
----------------------- --- ----------
Schnyder                Jan        345
Weber                   Jan        234
Keller                  Jan        596
Meyer                   Jan        987
Holzer                  Jan        509
Mⁿller                  Jan        456
Binggeli                Jan        487
Stoller                 Jan        596
Marty                   Jan         94
Studer                  Jan        784
Schnyder                Feb        223
Weber                   Feb        234
Keller                  Feb        276
Meyer                   Feb        345
Holzer                  Feb        154
Mⁿller                  Feb        125
Binggeli                Feb        347
Stoller                 Feb        237
Marty                   Feb        234
Studer                  Feb        567
Schnyder                Mar        122
Weber                   Mar        123
Keller                  Mar        347
Meyer                   Mar        645
Holzer                  Mar        876
Mⁿller                  Mar        678
Binggeli                Mar        458
Stoller                 Mar        976
Marty                   Mar        235
Studer                  Mar        235
Schnyder                Apr        345
Weber                   Apr        457
Keller                  Apr        134
Meyer                   Apr        567
Holzer                  Apr        347
Mⁿller                  Apr        235
Binggeli                Apr        347
Stoller                 Apr        876
Marty                   Apr        763
Studer                  Apr        753
Schnyder                May        324
Weber                   May        456
Keller                  May        743
Meyer                   May        834
Holzer                  May        146
Mⁿller                  May        878
Binggeli                May        235
Stoller                 May        346
Marty                   May         54
Studer                  May        124
Schnyder                Jun        244
Weber                   Jun        287
Keller                  Jun        545
Meyer                   Jun        567
Holzer                  Jun        788
Mⁿller                  Jun        237
Binggeli                Jun        864
Stoller                 Jun        567
Marty                   Jun        567
Studer                  Jun        575
Schnyder                Jul        123
Weber                   Jul        234
Keller                  Jul        216
Meyer                   Jul        789
Holzer                  Jul        174
Mⁿller                  Jul        567
Binggeli                Jul        689
Stoller                 Jul        126
Marty                   Jul        237
Studer                  Jul        864
Schnyder                Aug        123
Weber                   Aug        123
Keller                  Aug        456
Meyer                   Aug        234
Holzer                  Aug        986
Mⁿller                  Aug        237
Binggeli                Aug        235
Stoller                 Aug        879
Marty                   Aug        457
Studer                  Aug        235
Schnyder                Sep        345
Weber                   Sep        678
Keller                  Sep        124
Meyer                   Sep        678
Holzer                  Sep        568
Mⁿller                  Sep        788
Binggeli                Sep        764
Stoller                 Sep        125
Marty                   Sep        325
Studer                  Sep        753
Schnyder                Oct        121
Weber                   Oct        656
Keller                  Oct        753
Meyer                   Oct        973
Holzer                  Oct        246
Mⁿller                  Oct        237
Binggeli                Oct        964
Stoller                 Oct        568
Marty                   Oct        753
Studer                  Oct        864
Schnyder                Nov        345
Weber                   Nov        341
Keller                  Nov        346
Meyer                   Nov        456
Holzer                  Nov        324
Mⁿller                  Nov        324
Binggeli                Nov        624
Stoller                 Nov        124
Marty                   Nov        577
Studer                  Nov        634
Schnyder                Dec        197
Weber                   Dec        567
Keller                  Dec        456
Meyer                   Dec        125
Holzer                  Dec        987
Mⁿller                  Dec        778
Binggeli                Dec        347
Stoller                 Dec        753
Marty                   Dec        346
Studer                  Dec        678

120 rows selected.

SCOTT@orcl_11gR2>

Previous Topic: Data Pump and resumable_timeout
Next Topic: Problem Loading Data into Table- Tow fields appended & Converted into DateTime
Goto Forum:
  


Current Time: Wed Jul 30 10:54:45 CDT 2014

Total time taken to generate the page: 0.11793 seconds