如何将来自多个数据库的数据组合到一个结果集中。 （新旧系统）

Question

I have an old and a new database with customer data. And now I have to remember if the customer I'm looking for is part of the new system, or the old system before i search - in my application. That's kind of a hassel.
I would like to join the two result-sets together and throw them into one single mega overview in my application - which is a table with a search function.

What would be the best practise for this?

Both the old and new database structure (system) consists of multiple tables joined together in order to get the necessary information needed. And they're not entirely equal.
The new system is splitted into more tables, and there are added a few new ones, to meet the needs when the business expanded their services.

But the result that is returned to the viewer is the same.

Would it be possible to just kind of merge the two arrays, that holds the result-set from the database query, together? - and then do a while-loop or foreach-loop on that?

Not really sure how yet, but if that's a solution I would appreciate some help looking in the right direction..

Answer 1

Generally, you can combine results from multiple selects or tables using UNION or even UNION ALL on the database level. You just have to take care that your tables/selects return the same columns.

For example:

-- old table/database
select t.customername as name, t.street as street, t.city as city 
from old_data.customers t
where t.name like 'M%'
union 
- new table/database
select p.name as name, a.street as street, a.city as city
from new_data.person p
inner join new_data.address a on p.address_id = a.id
where p.name like 'M%'

With UNION it does not matter which names the tables or columns in the single SELECT statements have. What counts is that there are the same amount of columns and that they have compatible types.

The actual problem may lie in accessing the tables from multiple databases. The solution for that depends on the DBMS you are using and where your old and new databases are located.

If both databases are stored on the same server, as in your case, most DMBS (such as MySQL) will work for the query above (you prepend the database name to the table name as in database.table and you are good).

If both databases were not stored on the same server, then the solution would depend on the DBMS. Oracle, for example, lets you specify database links, which you can use to span queries across multiple servers. Or, you can cache remote tables using materialized views.

With MySQL, there are different possibilities, which are discussed in this question, e.g.