Home » RDBMS Server » Server Utilities » Load multiple tables from SQL* Loader (Oracle9i,windows)
Load multiple tables from SQL* Loader [message #529552] Tue, 01 November 2011 14:38 Go to next message
nsumankumar
Messages: 5
Registered: November 2010
Location: banglore
Junior Member

Hi

I Have Flat file with 20columns of data,My intention is to load into two tables(i.e TABLE1 WITH 10 columns,TABLE2 With remaining 10columns)

anybody please help me how to write Control file for the above scenario.

thanks in advance
suman
Re: Load multiple tables from SQL* Loader [message #529555 is a reply to message #529552] Tue, 01 November 2011 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Loader Case Studies, see Case Study 5: Loading Data into Multiple Tables - Loads data into multiple tables in one run

Regards
Michel

Re: Load multiple tables from SQL* Loader [message #529557 is a reply to message #529552] Tue, 01 November 2011 15:21 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
This is a perfect case where External Tables would make this easy. I do not think it is possible even with the link Michel sent as that would be to load "rows" into separate tables, not columns (although I do admin I did not look at the link, because from what I remember of the case studies, they did not include this scenario, but maybe now they do).
Re: Load multiple tables from SQL* Loader [message #529559 is a reply to message #529557] Tue, 01 November 2011 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no need that all tables use the same columns in the example.
Barbara showed several examples here.
I also prefer external table as I'm far better in SQL than in SQL*Loader.

Regards
Michel
Re: Load multiple tables from SQL* Loader [message #529564 is a reply to message #529559] Tue, 01 November 2011 17:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Here is an example.

-- flat_file.dat:
a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,
z,y,x,w,v,u,t,s,r,q,p,o,n,m,l,k,j,i,h,g,


-- test.ctl:
LOAD DATA
INFILE flat_file.dat
INTO TABLE table1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(col1 POSITION(1),col2,col3,col4,col5,col6,col7,col8,col9,col10)
INTO TABLE table2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(fil1 FILLER POSITION(1), fil2 FILLER, fil3 FILLER, fil4 FILLER, fil5 FILLER,
fil6 FILLER, fil7 FILLER, fil8 FILLER, fil9 FILLER, fil10 FILLER,
col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)


-- tables:
SCOTT@orcl_11gR2> CREATE TABLE table1
  2    (col1   VARCHAR2(4),
  3  	col2   VARCHAR2(4),
  4  	col3   VARCHAR2(4),
  5  	col4   VARCHAR2(4),
  6  	col5   VARCHAR2(4),
  7  	col6   VARCHAR2(4),
  8  	col7   VARCHAR2(4),
  9  	col8   VARCHAR2(4),
 10  	col9   VARCHAR2(4),
 11  	col10  VARCHAR2(5))
 12  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE table2
  2    (col1   VARCHAR2(4),
  3  	col2   VARCHAR2(4),
  4  	col3   VARCHAR2(4),
  5  	col4   VARCHAR2(4),
  6  	col5   VARCHAR2(4),
  7  	col6   VARCHAR2(4),
  8  	col7   VARCHAR2(4),
  9  	col8   VARCHAR2(4),
 10  	col9   VARCHAR2(4),
 11  	col10  VARCHAR2(5))
 12  /

Table created.


-- load:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log


-- results:
SCOTT@orcl_11gR2> SELECT * FROM table1
  2  /

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
---- ---- ---- ---- ---- ---- ---- ---- ---- -----
a    b    c    d    e    f    g    h    i    j
z    y    x    w    v    u    t    s    r    q

2 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM table2
  2  /

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
---- ---- ---- ---- ---- ---- ---- ---- ---- -----
k    l    m    n    o    p    q    r    s    t
p    o    n    m    l    k    j    i    h    g

2 rows selected.

SCOTT@orcl_11gR2>

Re: Load multiple tables from SQL* Loader [message #529573 is a reply to message #529564] Wed, 02 November 2011 00:14 Go to previous message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks,

Michel
Previous Topic: Check the INSERT statements
Next Topic: Two Error: IMP-00017 ORA-03113 when imported a dmp file to oracle
Goto Forum:
  


Current Time: Sat Feb 29 07:39:58 CST 2020