Home » SQL & PL/SQL » SQL & PL/SQL » To Load a single table at a time from 4 different tables (merged 2)
To Load a single table at a time from 4 different tables (merged 2) [message #394796] Mon, 30 March 2009 06:30 Go to next message
Jan_ora
Messages: 2
Registered: March 2009
Junior Member
Hi

I like to load data from 4 different tables (similar structure) into a single table.
So can ny one tell me what are the available options for doing it?

regards,
Jan
Re: To load a single table at a time from four different tables... [message #394803 is a reply to message #394796] Mon, 30 March 2009 06:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
UNION (ALL)
JOIN
4 separate runs
etc
Re: To load a single table at a time from four different tables... [message #394807 is a reply to message #394796] Mon, 30 March 2009 06:53 Go to previous messageGo to next message
Jan_ora
Messages: 2
Registered: March 2009
Junior Member
Hi,


Thanks for the reply.

I exact thing is i need to do it at a time NOT one after the other.

rgrds
Jan
Re: To load a single table at a time from four different tables... [message #394811 is a reply to message #394807] Mon, 30 March 2009 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's still going to be UNION (ALL):
INSERT INTO table_1
(<column_list>)
SELECT (<column_list>)
FROM (SELECT <column_list_a>
      FROM   TABLE_A
     UNION ALL
      SELECT <column_list_a>
      FROM   TABLE_B
     UNION ALL
      SELECT <column_list_a>
      FROM   TABLE_C)
Re: To Load a single table at a time from 4 different tables (merged 2) [message #395038 is a reply to message #394796] Tue, 31 March 2009 02:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If you don't want to create new table from four existing tables, you can use WITH clause as

with combined as
(select col1 from t1
union all
select col1 from t2
union all
select col1 from t3)
select * from combined;


Or you can create VIEW also.

regards,
Delna
Re: To Load a single table at a time from 4 different tables (merged 2) [message #395055 is a reply to message #395038] Tue, 31 March 2009 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would you need a WITH clause for that?
How is your query different from the union all without the surrounding query?
Re: To Load a single table at a time from 4 different tables (merged 2) [message #395117 is a reply to message #395055] Tue, 31 March 2009 06:11 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Using only UNION ALL would not give facility to use that resultant record set in other manipulation. Morever he/she is not required to create table unnecessarily, if he/she use WITH clause.

regards,
Delna
Re: To Load a single table at a time from 4 different tables (merged 2) [message #395149 is a reply to message #395117] Tue, 31 March 2009 08:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wrap the union all in a set of parentheses and you can use it as an inner query. No need for with.
Previous Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table
Next Topic: Empty Collection
Goto Forum:
  


Current Time: Wed Dec 07 18:51:24 CST 2016

Total time taken to generate the page: 0.14489 seconds