• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Sql Loader

We have 10 column in the datafile and 3 column  in our staging table. We need to pick the 7th column out of the data file and append into the 2nd column of the staging table using sql loader.  This is what we have for syntax so far.

LOAD DATA
INFILE 'C:\DATA.txt'
BADFILE 'C:\DATA.bad'
DISCARDFILE 'C:\DATA.dsc'
APPEND INTO TABLE "STAGING_TABLE"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
  (COLUMN_1,  COLUMN_2,   COLUMN_3)
 
This syntax seems to insert the first 3 columns from the datafile to the 3 columns in the staging tables, it won't allow us to pick the 7th column for the datafile to insert into the 2nd column in the staging table.  This does not seem to work. Any help would be great
0
rrisal
Asked:
rrisal
  • 2
1 Solution
 
joebednarzCommented:
I would avoid using Sql Loader.  Try, instead, using externally organized tables:

SQL> create table MY_EXTERNAL_TABLE
  2      ( empl_id varchar2(3),
  3        last_name varchar2(50),
  4        first_name varchar2(50),
  5        ssn varchar2(9),
  6       email_addr varchar2(100),
  7        years_of_service number(2,0)
  8      )
  9      organization external
 10      ( default directory ORACLE_DIRECTORY_OBJECT
 11        access parameters
 12        ( records delimited by newline
 13          fields terminated by ','
 14        )
 15        location ('my_data_file.csv')  
 16    );

From there, you simply can do an INSERT accessing the "external" data in an SQL query.
0
 
joebednarzCommented:
In case you need it:

CREATE OR REPLACE DIRECTORY oracle_directory_object
AS 'C:\my_directory';

GRANT READ ON DIRECTORY oracle_directory_object TO user_name;

Then make sure your data file is in the directory specified...
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now