Data consolidation

[zik]

I have a requirement says that there three tables in different Oracle databases that need to be consolidated into one table.
In order words, all data in those tables will to be moved into one table in a data warehouse set up.
Can anyone give a head start on this.
I am sorry that I don't have any specific requirement on this.
My guess is that they probably want the data in those tables denormalized.
zik

 

[THoey]

If the tables are of the same structure, couldn't you possibly add a field to determine which server it came from (if necessary), create database links between your destination database and the source databases and then just write append queries to pull the data in? If you have a date field, you could pull in for a date range, in case this going to be a re-occurring task.

Hope that is a start... Terry M. Hoey

 

[carp]

The hardest part may be figuring out how the data in the three tables relate to one another. Once you have figured out how to join the tables, the rest is reasonably simple (although it may take a long time to run, depending on the sizes of the tables!). I would suggest trying to create a view that joins the tables - this allows you to get the look/feel of a table without incurring the overhead of creating a table. Once you have the view created and are satisfied that the data is correctly joined, you can use
CREATE TABLE new_table AS SELECT * FROM my_view;

If the tables are large, you might want to make small clones to experiment with:

CREATE TABLE clone1 AS SELECT * FROM big_table_1 WHERE rownum < 11;
.
.
.

 

[zik]

Thanks good people!
I knew it would be a challenge.
I am still gathering more info about these tables and their accompanying data.
If I get stuck in this, please expect another thread from me.
Thanks again.
zik