OraFAQ Forum: SQL & PL/SQL » Inserting data from multiple table into 1 table

Home » SQL & PL/SQL » SQL & PL/SQL » Inserting data from multiple table into 1 table (windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Inserting data from multiple table into 1 table [message #661640]

Sun, 26 March 2017 11:06

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?

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661642 is a reply to message #661640]

Sun, 26 March 2017 11:44

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661643 is a reply to message #661642]

Sun, 26 March 2017 11:45

John Watson
Messages: 8929
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.

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661644 is a reply to message #661642]

Sun, 26 March 2017 11:47

John Watson
Messages: 8929
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.

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661645 is a reply to message #661640]

Sun, 26 March 2017 12:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661646 is a reply to message #661642]

Sun, 26 March 2017 12:41

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.

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661647 is a reply to message #661645]

Sun, 26 March 2017 12:43

Dnmunde
Messages: 3
Registered: March 2017

Junior Member

thanks! i got one way atleast.

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661651 is a reply to message #661647]

Sun, 26 March 2017 22:34

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

Report message to a moderator

Re: Inserting data from multiple table into 1 table [message #661652 is a reply to message #661651]

Mon, 27 March 2017 00:48

Michel Cadot
Messages: 68641
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.)

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 01:23:24 CDT 2024