sql loader control file

I  am loading data from a csv file, and I am getting customer_id value from csv file, I want to insert the same customer_id into 3columns. 2columns are in 1 table and another column is in another column. The same thing in the form of query is

Insert into to2_user (customer_id, login, password) select customer_id,customer_id,customer_id from dps_user.

The control file is not allowing me to reference customer_id for multiple places because it has to read the data from the beginning of the row.

How to mention this in control file. Please help.
Ravi_ChintadaAsked:
Who is Participating?
 
Zlatin ZlatevTechnical ArchitectCommented:
See SQL Loader documentation at http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm
section "Using POSITION with Multiple Table Loads"

When you specify POSITION() for the first column of the first table, the position is calculated relative to the beginning of the logical record (which allows you to reference the customer_id value twice)

P.S. This should be in the Oracle forum btw.
0
 
slightwv (䄆 Netminder) Commented:
Given the following table and control file (I used inline data for test purposes):

I used:
sqlldr username/password control=myControl.ctl

I end up with
SQL> select * from tab1;
a a

1 row selected.

SQL> select * from tab2;
a

1 row selected.

drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1));

drop table tab2 purge;
create table tab2 (col1 char(1));

Open in new window

load data
 infile *
 truncate INTO TABLE Tab1
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
col1,
col2 ":col1"
)
 INTO TABLE Tab2
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
col1 position(1)
)
begindata
a|b

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Ravi_Chintada,

The reason you never received a response to this is it is strictly Oracle related and was not asked in an Oracle Zone.

The Experts in the initial zones should have let you know this so it could have been addresses long before now.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you provide your sample data in the data file for few records so it becomes easy to come up with a control file for sql loader ?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
Is extra data necessary?  I took data, added it to two columns in a single table and another column in a different table.

Seems to meet the requirements.
0
 
Zlatin ZlatevTechnical ArchitectCommented:
I am not sure if the author needs more assistance with this one.
Still I think my comment has pointed him in the right direction, so it is a helpful comment, even if it may not be the complete solution to the problem.
0
 
slightwv (䄆 Netminder) Commented:
I tend to agree and would suggest a split between:
http:#a35322839
http:#a35326344
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.