SQL/MYSQL: How to display 3 identical tables (from 3 different database files) as if it is a single table

Question

I have an ORACLE table with about a 5 million records, and growing. They are using access to export the data to (From Oracle to Access). They won't change their system, regardless of what I suggest them.

So, in order to be able to work with the data and export it to excel, from access, i had to split the data into 3 database files. In example, let's say I have tables

database1.TBL_A_to_G

database2.TBL_H_to_O

database3.TBL_P_to_Z

Since they come from a single table, they have the same fields. Let's say,

Field1, Field2, Field3...Field30

How can I get a query, in access, where I am getting the data from the 3 tables as if it is one?

I Tried

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_to_G

UNION

SELEC Field1, Field2, Field3, Field4, ... Field30 FROM TBL_H_to_O

-I have to save this query as TBL_A_O and then,

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_O

UNION

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_P_to_Z

The problem with this approach, is that I can't union the three tables in a single query statement. I get an error message, saying that I can union that many fields. Also, am am still having some performance problems. I need to find a way to do this with performance in mind.

I am open to alternatives as well. The objective is to get the data from those 3 tables (in three different databases) and then export them as queries (With formulas, etc) into excel.

"I get an error message, saying that I can union that many fields." Please provide the exact error message. — cdhowie
– cdhowie, Commented Mar 25, 2013 at 1:11
I know you said they won't change the way they do things but.. you do know newer Excel can pull data directly from Oracle right? — sam yi
– sam yi, Commented Mar 25, 2013 at 2:26
@Sam Yi, starting with Excel 2007, these are the new limits: 1,048,576 rows by 16,384 columns. — Tom Collins
– Tom Collins, Commented Mar 25, 2013 at 3:08

Gordon Linoff · Accepted Answer · 2013-03-25 01:11:12Z

0

Try using union all instead:

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_to_G
UNION ALL
SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_H_to_O
union all
SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_P_to_Z

With only 30 fields per table, this should be fine for Access.

answered Mar 25, 2013 at 1:11

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

crazylpfan Over a year ago

However, keep in mind that UNION ALL doesn't remove duplicates.

cdhowie Over a year ago

@crazylpfan If the tables represent partitioned data, then there should not be any duplicates.

Omar Juvera Over a year ago

@cdhowie Yes, the three tables are partitions from the source table (Source: oracle; Access databases are the "data receptacles", partitioned into tree to be able to work faster). The source data has no duplicate fields.

Wander · Accepted Answer · 2013-03-25 02:18:10Z

0

First you need to know if you want to repeat registers, if so use UNION ALL otherwise use UNION. Problably you have to update the statistics from ACCESS to better performance. About the field problem, aren´t you using the wrong name to acess the data, remember that UNION/UNION ALL uses the first query´s fields names to the result table.

answered Mar 25, 2013 at 2:18

Wander

1

1 Comment

Wander Over a year ago

Try use * instead of the field names judt for test, maybe you are using too much characters on the select.

Collectives™ on Stack Overflow

SQL/MYSQL: How to display 3 identical tables (from 3 different database files) as if it is a single table

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related