[thelist] MYSQL merging multiple tables

[Matt Warden]

On Wed, Sep 3, 2008 at 10:18 AM, Jeremy Weiss <eccentric.one at gmail.com> wrote:
> I have a database that contains several tables. Each table is updated once a day from various sources. After they are updated, I'd like to merge them into a single table. Most of the fields in each of the tables are the same, but there are some differences. What would be the easiest way to go about combining these tables into one?
>

This is actually a pretty common scenario. Here's how we handle it. We
have a staging schema where we have the tables which match the
incoming files exactly. From file to stage table is a direct 1-1 load.
Then we select from this staging table in a way that manipulates the
data as necessary (moves columns around, combines columns, splits
columns, translates values, removes duplicates, etc.) and insert into
the primary table in the main schema.

We use Informatica because of the large volume, but you could just as
easily use a stored procedure with a pretty simple "insert into ...
select from" construct.


-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.