Home » RDBMS Server » Server Utilities » How to get parent table column reference and put in child table while loding data using sql loader (Oracle-11.2 - windows)
How to get parent table column reference and put in child table while loding data using sql loader [message #596538] Tue, 24 September 2013 08:01 Go to next message
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
Junior Member
Hi, I am new to SQL loader utility.
I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and
store in child table with child data. i am not able to find how to get parenet refernce id using control file.
In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record
and put into child table for c_name test1 and test2 records.

Please suggest any solution. Please refere.

mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

1. table parent (P_ID, P_Name)
2. table child (C_ID, C_Name, P_ID)

i need data bello way.

Parent Table data:

P_ID P_Name
----- ------
1 A
2 B
3 C
4 D

Child Table data:

C_ID C_Name P_ID
---- ------ ----
1 test1 1
2 test2 1
3 test3 2
4 test4 2
5 test5 3
6 test5 3
7 test6 4
8 test6 4

My controll file
-------------------

LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(p_id SEQUENCE(COUNT, 1),
p_name position(3:15))
INTO TABLE child
when (1:1) = 'c'
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(c_id SEQUENCE(COUNT, 1),
c_name position(3:15),
p_id EXPRESSION "(SELECT MAX(P_ID) FROM PARENT WHERE P_name='A' AND P_name='B' AND P_name='C' AND P_name='D')"
)

able to load parent data and generate P_ID, but i am not able to get P_ID for child records. Please help me.

Thnaks in advance.

[Updated on: Tue, 24 September 2013 08:15]

Report message to a moderator

icon5.gif  Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596539 is a reply to message #596538] Tue, 24 September 2013 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post a complete test case: "create table" for parent and child tables, some data (the csv file and the control file you gave don't match, see "when (1:1)").

Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596545 is a reply to message #596539] Tue, 24 September 2013 11:48 Go to previous messageGo to next message
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
Junior Member
Parent Table :
create table parent(p_id number primary key, p_name varchar(30))

Child Table :
create table child(c_id number primary key, c_name varchar(30), P_Id number FOREIGN KEY REFERENCES Persons(P_Id))

Relation : One to Many 

mydata.csv
---------------
P_Name,C_Name;
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

I need to load 'mydata.csv' data  bellow parent and child table way.

Parent:
P_ID  	P_Name
1	A
2	B
3	C
4	D

Child Table :
C_ID  	C_Name	P_ID (Parent P_ID reference)
1	test1	1
2	test2	1
3	test3	2
4	test4	2
5	test5	3
6	test6	3
7	test7	4
8	test8	4


i hope you you understand my issue.

[Updated on: Tue, 24 September 2013 13:36] by Moderator

Report message to a moderator

icon3.gif  Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596547 is a reply to message #596545] Tue, 24 September 2013 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'd use an external table for this.

But wait a bit, Barbara may come with a nice solution in a while.

Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596572 is a reply to message #596547] Tue, 24 September 2013 16:04 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I managed to do that. I slightly modified both of your tables; PARENT by adding the UNIQUE key (which rejects duplicates) and CHILD table by adding additional column which is supposed to accept the leftmost value from the CSV file; as we don't need it, I used BOUNDFILLER and used its value later when calling a function.

(By the way, CREATE TABLE statements you posted are invalid; pay attention to what you post next time.)

SQL> create table parent
  2    (p_id      number primary key,
  3     p_name    varchar(30) unique
  4    );

Table created.

SQL> create table child
  2    (c_id      number primary key,
  3     ignore_me varchar2(30),
  4     c_name    varchar(30),
  5     P_Id      number constraint fk_cp REFERENCES parent(P_Id)
  6    );

Table created.

SQL> create or replace function f_id (par_name in varchar2)
  2    return parent.p_id%type
  3  is
  4    retval parent.p_id%type;
  5  begin
  6    select p.p_id
  7      into retval
  8     from parent p
  9     where p.p_name = par_name;
 10    return (retval);
 11  exception
 12    when no_data_found then
 13      return null;
 14  end;
 15  /

Function created.

SQL>
I put input data into control files (note the BEGINDATA sections in both files; you'd use a CSV file instead - I didn't feel like creating one).

A control file that loads the PARENT table (test4.ctl):
LOAD DATA
INFILE *
REPLACE
INTO TABLE parent
FIELDS TERMINATED BY ","
trailing nullcols
(   p_id     sequence(1, 1),
    p_name
)
begindata
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

A control file that loads the CHILD table (test4_a.ctl):
LOAD DATA
INFILE *
REPLACE
into table child
fields terminated by ","
trailing nullcols
  (c_id      sequence(100, 1),
   ignore_me boundfiller,
   c_name    terminated by ";",
   p_id      expression "f_id(:ignore_me)"
  )

begindata
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

Execution:
SQL> $sqlldr scott/tiger@xe control=test4.ctl log=test4.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Ruj 24 22:58:04 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 7
Commit point reached - logical record count 8

SQL> $sqlldr scott/tiger@xe control=test4_a.ctl log=test4_a.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Ruj 24 22:58:11 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 7
Commit point reached - logical record count 8

SQL> select * from parent;

      P_ID P_NAME
---------- ------------------------------
         1 A
         3 B
         5 C
         7 D

SQL> select * from child;

      C_ID IGNORE_ME                      C_NAME                               P_ID
---------- ------------------------------ ------------------------------ ----------
       100                                 test1                                  1
       101                                 test2                                  1
       102                                 test3                                  3
       103                                 test4                                  3
       104                                 test5                                  5
       105                                 test6                                  5
       106                                 test7                                  7
       107                                 test8                                  7

8 rows selected.

SQL>


A non-working solution that uses only one control file

Sorry for not knowing how to do that any better using SQL*Loader.
Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596683 is a reply to message #596572] Wed, 25 September 2013 11:58 Go to previous messageGo to next message
sureshpediveeti
Messages: 3
Registered: September 2013
Location: delhi
Junior Member
Thanks for quick solution. It is really helpful to me.

But in my schema parent and child tables are already exist with data.

In my parent table p_name field does not have unique constraint and in child table can not add extra column(ignore_me).
I don't have permission to disturb existing tables structure. My tables exist as shown bellow.

create table parent
(p_id number primary key,
p_name varchar(30)
);

create table child
(c_id number primary key,
c_name varchar(30),
P_Id number constraint fk_cp REFERENCES parent(P_Id)
);
Data
-------
col1 col2
---- ----
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

I need to avoid to load repeated data in col1, this data goes to parent table p_name. Please suggest me any solution based on my requirement.

Thanks in advance.

[Updated on: Wed, 25 September 2013 12:00]

Report message to a moderator

Re: How to get parent table column reference and put in child table while loding data using sql loader [message #596703 is a reply to message #596683] Wed, 25 September 2013 13:18 Go to previous message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I don't have permission to disturb existing tables structure

a) maybe you should get it
b) try external tables (as Michel suggested)
c) wait for someone else's solution
Previous Topic: special character while loading
Next Topic: SQL LOADER
Goto Forum:
  


Current Time: Fri Apr 18 06:12:22 CDT 2014

Total time taken to generate the page: 0.16591 seconds