Solved

sql loader control file

Posted on 2010-11-18
10
1,014 Views
Last Modified: 2013-12-08
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.
0
Comment
Question by:Ravi_Chintada
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
zlatev earned 250 total points
ID: 35322839
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 35326344
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35326404
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
Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35338506
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35338549
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
 
LVL 8

Expert Comment

by:zlatev
ID: 35767930
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35768692
I tend to agree and would suggest a split between:
http:#a35322839
http:#a35326344
0

Featured Post

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
looking for guidance on Oracle Sql Formatting standards 9 43
send to option in chrome 11 55
Google Chrome GPO Not Applying 5 79
Shared Service Environment 2 54
Several part series to implement Internet Explorer 11 Enterprise Mode
SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question