Home » SQL & PL/SQL » SQL & PL/SQL » Inserting data from multiple table into 1 table (windows)
Inserting data from multiple table into 1 table [message #661640] Sun, 26 March 2017 11:06 Go to next message
Dnmunde
Messages: 3
Registered: March 2017
Junior Member
How can we insert data from multilple tables into 1 table?
How many methods?
please can any one give examples?
Re: Inserting data from multiple table into 1 table [message #661642 is a reply to message #661640] Sun, 26 March 2017 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Why are you violating First Normal Form by duplicating data across multiple tables?

I refuse to provide any example that is Worst Practice.
Re: Inserting data from multiple table into 1 table [message #661643 is a reply to message #661642] Sun, 26 March 2017 11:45 Go to previous messageGo to next message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to describe what you are wanting to do in greater detail, I don't understand the question.
Re: Inserting data from multiple table into 1 table [message #661644 is a reply to message #661642] Sun, 26 March 2017 11:47 Go to previous messageGo to next message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
The problem could be, for example, loading data from several sources into Data Warehouse tables. Or creating a materialized view container table. Or consolidating audit trails.
Re: Inserting data from multiple table into 1 table [message #661645 is a reply to message #661640] Sun, 26 March 2017 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

insert into target
select * from source1
union all
select * from source2
...
/

Re: Inserting data from multiple table into 1 table [message #661646 is a reply to message #661642] Sun, 26 March 2017 12:41 Go to previous messageGo to next message
Dnmunde
Messages: 3
Registered: March 2017
Junior Member
Yes that's right. but i am more into de-normalizing databases. so most of the times i will be doing this job and i want to know by how many ways i can do this.
Re: Inserting data from multiple table into 1 table [message #661647 is a reply to message #661645] Sun, 26 March 2017 12:43 Go to previous messageGo to next message
Dnmunde
Messages: 3
Registered: March 2017
Junior Member
thanks! i got one way atleast.
Re: Inserting data from multiple table into 1 table [message #661651 is a reply to message #661647] Sun, 26 March 2017 22:34 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

one is the method using union all as already mentioned above.

The other is having a intermediate staging table which reads all the data from various data sources and loads into staging table before loading into target table. The staging table is purged everyday before it is loaded. When it comes to reading the data from the various sources you can use database link or external table concept depending on your requirement.

Alternatively you can use ETL tools like Informatica to read the source tables and load into target

garan
Re: Inserting data from multiple table into 1 table [message #661652 is a reply to message #661651] Mon, 27 March 2017 00:48 Go to previous message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why loading into a stage table to then load into the target one?
Why this useless step?
You just move the problem from the target table to the stage one.

I provided a solution that guarantees the data are from the same point in time. An ETL will not guarantee this and so the data may not be consistent.

Another is to start a transaction in serializable mode and use INSERT SELECT for each source table.
(About the consistent point in time, I assumed there, and in the previous solution, that all tables are in the same database.)

Previous Topic: Receiving Email Messages through Oracle procedure.
Next Topic: Grouping for varying weeks
Goto Forum:
  


Current Time: Mon Nov 19 01:15:30 CST 2018