Home » SQL & PL/SQL » SQL & PL/SQL » Insering data into one single table by taking data from 2 different tables (Oracle 9)
Insering data into one single table by taking data from 2 different tables [message #392655] Wed, 18 March 2009 15:23 Go to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
Suppose table A contains 5 column and table B contains 4 columns..

How to insert all those 5+4=9 column in a single row into C table in a single procedure..
pls suggest..

Ex: A contains

1 st row :1 2 3 4 5
2 nd row :11 22 33 44 55

and B contains

1 st row :6 7 8 9 10
2 nd row :66 77 88 99 100


i Need to insert data into C table like this

Table C

1 st row :1 2 3 4 5 6 7 8 9 10
2 nd row :11 22 33 44 55 66 77 88 99 100



Kindly advice how to do this in a single procedure..
Re: Insering data into one single table by taking data from 2 different tables [message #392658 is a reply to message #392655] Wed, 18 March 2009 15:44 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Rows in a table have NO inherent order.
Rows in a table are like balls in basket.
Which ball in the basket is the first ball & which is the next ball?

The "requirements" are nonsensical.
Re: Insering data into one single table by taking data from 2 different tables [message #392705 is a reply to message #392655] Thu, 19 March 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to define an order on your tables to indicate to Oracle which row is first and which one is second...

Regards
Michel
Re: Insering data into one single table by taking data from 2 different tables [message #392734 is a reply to message #392655] Thu, 19 March 2009 01:58 Go to previous messageGo to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
I Wanted to insert 10 fields/columns in a single row among which 5 are present in one row in one table and other 5 are in some other table.

Please suggest.
Re: Insering data into one single table by taking data from 2 different tables [message #392739 is a reply to message #392734] Thu, 19 March 2009 02:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But how would you determine which row in table A should be joined to which row in table B?
Re: Insering data into one single table by taking data from 2 different tables [message #392740 is a reply to message #392739] Thu, 19 March 2009 02:27 Go to previous messageGo to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
I got it Now.Can you explain how to Define the order on tables and then how to proceed on this.I am in learning phase ,so need help on this.

Thanks.

Re: Insering data into one single table by taking data from 2 different tables [message #392745 is a reply to message #392740] Thu, 19 March 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you explain how to Define the order on tables

You use ORDER BY clause when you query it.

Regards
Michel
Re: Insering data into one single table by taking data from 2 different tables [message #392763 is a reply to message #392655] Thu, 19 March 2009 04:16 Go to previous messageGo to next message
sankar7
Messages: 7
Registered: October 2005
Location: Dhaka
Junior Member
Hi,
You can try like this procedure

create or replace procedure tt is

begin
insert into test3(select id, id2 from test);
insert into test3(select id, id2 from test2);
end;

thanks

--
Re: Insering data into one single table by taking data from 2 different tables [message #392769 is a reply to message #392763] Thu, 19 March 2009 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/5263/0/

Re: Insering data into one single table by taking data from 2 different tables [message #392793 is a reply to message #392769] Thu, 19 March 2009 05:24 Go to previous messageGo to next message
piyali.sriza@gmail.com
Messages: 6
Registered: March 2009
Junior Member
The query written will not help.

Suggestions please.
Re: Insering data into one single table by taking data from 2 different tables [message #392800 is a reply to message #392655] Thu, 19 March 2009 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Add a column to both tables that you can use to link them.
It's your data, we don't know how it relates, only you do.
Re: Insering data into one single table by taking data from 2 different tables [message #392802 is a reply to message #392800] Thu, 19 March 2009 05:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've got a way of ordering the two sets of data, it's easy:
create table test_149 (ord_col number, data_col   varchar2(100));
create table test_150 (ord_col number, data_col   varchar2(100));

insert into test_149 values (1,'1 2 3 4 5 ');
insert into test_149 values (2,'11 22 33 44 55');
 

insert into test_150 values (1,'6 7 8 9 10');
insert into test_150 values (2,'66 77 88 99 100');

select t1.data_col||t2.data_col
from   test_149 t1
      ,test_150 t2
where  t1.ord_col = t2.ord_col;
Previous Topic: Order By Decode Variable UNION Issue!!
Next Topic: using nested tables with CAST as normal table (merged 3)
Goto Forum:
  


Current Time: Sun Dec 04 04:32:45 CST 2016

Total time taken to generate the page: 0.10693 seconds