Home » SQL & PL/SQL » SQL & PL/SQL » Data transfer (merged)
icon5.gif  Data transfer (merged) [message #265917] Fri, 07 September 2007 19:33 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hello All

I got a very basic question.
Can you please guide me how can we transfer data from one source table to target table. let me explain myself;

I have one table and im moving data from the table to two normalized tables. they are linked with pk and fk relation ship. How can i do that?
Thanks for you help
y2k
Re: Data transfer [message #265918 is a reply to message #265917] Fri, 07 September 2007 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How can i do that?
By using 2 INSERT statements.

Please read & follow the posting guidelines as stated in the STICKY post found at the top of this forum.
Re: Data transfer [message #265930 is a reply to message #265917] Sat, 08 September 2007 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a description of your tables, what's the input, waht are the output...

Regards
Michel
Re: Data transfer [message #265946 is a reply to message #265917] Sat, 08 September 2007 01:06 Go to previous messageGo to next message
abhay2007
Messages: 2
Registered: September 2007
Junior Member
Hellow,

You should write query and with cursor and insert statement for where want to store data in table .


Abhay

[mod-edit] removed illiterate IM speak words.

[Updated on: Mon, 10 September 2007 07:57] by Moderator

Report message to a moderator

Re: Data transfer [message #265954 is a reply to message #265946] Sat, 08 September 2007 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you would not.
This is the last solution when you can't do anything else.

Regards
Michel
icon5.gif  Inserting in Relational Tables [message #267249 is a reply to message #265917] Wed, 12 September 2007 17:45 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hello All

I got a some problem. let me explain it;

i got a source table with the following example records;
vend_id invoice_nbr desc
v001 inv001 obj1
v001 inv001 obj2
v001 inv001 obj3
v002 inv002 obj2
v002 inv003 obj2
v002 inv003 obj5
v002 inv003 obj21

i want this data to go to two different tables

table 1:
id(PK) vend_id invoice_nbr
1 v001 inv001
2 v002 inv002
3 v002 inv003

table2:
id(FK) desc
1 obj1
1 obj2
1 obj3
2 obj2
3 obj2
3 obj5
3 obj21



So the table 1 and 2 have relationship of pk and fk. i want to insert the data in table1 and then from there i have to relate them to table 2. how can i do that. im trying to write a procedure but i get stuck in inserting the data in the table2 with the relation to the parimary key.
please help.
Thanks
Re: Inserting in Relational Tables [message #267275 is a reply to message #267249] Wed, 12 September 2007 23:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I don't see any problem. After inserting data in table1 then commit and then insert data in table 2.
Re: Inserting in Relational Tables [message #267320 is a reply to message #267249] Thu, 13 September 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Inserting in Relational Tables [message #267341 is a reply to message #267320] Thu, 13 September 2007 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
removed duplicate entry
Re: Inserting in Relational Tables [message #267375 is a reply to message #267249] Thu, 13 September 2007 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by 1,2;
VEND_ID    INVOICE_NBR     DESCR
---------- --------------- ----------
v001       inv001          obj1
v001       inv001          obj2
v001       inv001          obj3
v002       inv002          obj2
v002       inv003          obj2
v002       inv003          obj5
v002       inv003          obj21

7 rows selected.

SQL> insert all
  2  into t1 values (id, vend_id, invoice_nbr)
  3  into t2 values (id, descr)
  4  (select rownum id, vend_id, invoice_nbr, descr from t)
  5  /

14 rows created.

SQL> select * from t1 order by id;
        ID VEND_ID    INVOICE_NBR
---------- ---------- ---------------
         1 v001       inv001
         2 v001       inv001
         3 v001       inv001
         4 v002       inv002
         5 v002       inv003
         6 v002       inv003
         7 v002       inv003

7 rows selected.

SQL> select * from t2 order by id;
        ID DESCR
---------- ----------
         1 obj1
         2 obj2
         3 obj3
         4 obj2
         5 obj2
         6 obj5
         7 obj21

7 rows selected.

By the way, "desc" is a reserved word don't use it for a column name.

Regards
Michel
Re: Data transfer (merged) [message #267381 is a reply to message #265917] Thu, 13 September 2007 02:28 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi Michel



NOTE:

i want this data to go to two different tables

table 1:
id(PK) vend_id invoice_nbr 
1 v001 inv001                               
2 v002 inv002 
3 v002 inv003 

table2:
id(FK) desc
1 obj1
1 obj2
1 obj3
2 obj2
3 obj2
3 obj5
3 obj21


Regards,
Thani...
Re: Data transfer (merged) [message #267421 is a reply to message #267381] Thu, 13 September 2007 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what did I post?
Oh! you want distinct. Then try to modify what I posted... or do it in 2 passes.

Regards
Michel

[Updated on: Thu, 13 September 2007 04:38]

Report message to a moderator

Re: Data transfer (merged) [message #267437 is a reply to message #267421] Thu, 13 September 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by 1,2;
VEND_ID    INVOICE_NBR     DESCR
---------- --------------- ----------
v001       inv001          obj1
v001       inv001          obj2
v001       inv001          obj3
v002       inv002          obj2
v002       inv003          obj2
v002       inv003          obj5
v002       inv003          obj21

7 rows selected.

SQL> insert into t1 select rownum, t.* from (select distinct vend_id, invoice_nbr from t) t;

3 rows created.

SQL> insert into t2 
  2  select t1.id, t.descr from t, t1 where t1.vend_id = t.vend_id and t1.invoice_nbr = t.invoice_nbr;

7 rows created.

SQL> select * from t1 order by id;
        ID VEND_ID    INVOICE_NBR
---------- ---------- ---------------
         1 v001       inv001
         2 v002       inv002
         3 v002       inv003

3 rows selected.

SQL> select * from t2 order by id;
        ID DESCR
---------- ----------
         1 obj1
         1 obj2
         1 obj3
         2 obj2
         3 obj2
         3 obj5
         3 obj21

7 rows selected.

Regards
Michel
Re: Data transfer (merged) [message #267444 is a reply to message #267437] Thu, 13 September 2007 05:16 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thank you Michel


Thani..
icon14.gif  Re: Data transfer (merged) [message #267627 is a reply to message #265917] Fri, 14 September 2007 00:06 Go to previous message
kham2k
Messages: 34
Registered: May 2007
Member
Thanks allot everyone

This is great!!!
Previous Topic: get name of files and copy this files
Next Topic: Grant and Revoke commands
Goto Forum:
  


Current Time: Thu Dec 08 08:27:34 CST 2016

Total time taken to generate the page: 0.19634 seconds