CODEWITHSUNDEEP
oracle-databaseweblogicoracle-sqldevelopersql-loader
MaToXz
MaToXz

Reputation: 61

SQL* Loader mapping DataFile Fields in different tables columns

I'm trying to load data from a Datafile in different tables, I read a lot about field declaration and delimitation(Position(n:n), terminated by ). The point is than I'm not sure how to do what I need to do. Let me explain this with an example.

I have two tables (person, phone): person_table( person_id_pk, person_name) - phone_table(person_id_pk, phone)

I have a datafile with:

$ datafile.txt
1,jack pierson,+13526985442
2,Katherine McLaren,+15264586548

My point is, when I'm declaring my ConfigFile.ctl, how do I specify than the field number 3 (phone field) should be insert or append into "phone_table", and the others two fields (person_id, person_name) should be insert or append into "person_table"

Considering than the fields are not fixed length, my reference is the field position. (Field datafile position)

I was thinking to try something like

$configfile.ctl
LOAD DATA
INFILE datafile.txt
APPEND
INTO TABLE person_table
(
  person_id_pk POSITION (*)   INTEGER EXTERNAL TERMINATED BY "," ,
  person_name POSITION(*+1) CHAR(30) TERMINATED BY "," 
)

INTO TABLE phone_table
(
  person_id_fk POSITION (*) INTEGER EXTERNAL TERMINATED BY ","
  phone ------> Right here is my point, how can I specify to SQL Loader than here 
                should be the field number 3 from datafile
)

I hope you guys get my point. it is a HUGE issue for me, because i'm dealing with CSV files which contains 60, 80, even 100 fields (columns based on Excel File). And every fields or group of fields could be in different tables.

I really appreciate the guide and help you could grant me. I'm probably wrong about my example and controlfile declarations, I haven't implemented anything yet. So I'm open to every suggest you could give me.

Upvotes: 0

Views: 927

Answers (1)

Gary_W
Gary_W

Reputation: 10360

Your control file should look like this. The second "INTO TABLE" Uses POSITION(1) to move the logical "pointer" back to the start of the current line so it can be read again. then the name is skipped by defining it as a FILLER.

LOAD DATA
INFILE datafile.txt
APPEND
INTO TABLE person_table
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(
  person_id_pk  INTEGER EXTERNAL,
  person_name   CHAR(30) 
)

INTO TABLE phone_table
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(
  person_id_fk POSITION(1) INTEGER EXTERNAL,
  x_name       FILLER,
  phone        CHAR(12)
)

Upvotes: 1

Related Questions