• The two tables are storing slightly different information on the exact same set of 40M financial transactions -- that is, they contain the same number of rows, and so will the merged table.

  Or

• The two tables are storing the same information on two different sets of transactions -- they probably have different row counts, and the row count of the new table will be the sum of row counts from the two original tables.

  Or

• The two tables store the same information, but there is some overlap in their content, so the row count of the new table will be the sum of: row count of records that are common to both input tables, plus row count of records unique to table 1, plus row count of records unique to table 2.

Any other situation (i.e. table structure is different in the two tables, and some or all records are unique to each table) will be a mess for merging, unless you can somehow compute, deduce, recover, or otherwise fill in the fields that are unique to table 1 for rows that come from table 2, and vice-versa.

In the simplest case (table structures are identical), this is not a perl question. You either pick one table to be the "keeper" (or define a new table to be the "union"), and insert into that table from the other(s), using the extra clause "ON DUPLICATE KEY UPDATE ..." at the end of the insert statement.

In the less simple case (table structures are different, but the two input tables are storing the exact same set of transactions), it's still a pure SQL operation -- create a new "merge" table with the union of all columns from the other two, and do a join of those two in order to select into the new one.

In the nasty case (different structures and different sets of transactions)... well, good luck with that. I'd say you would need some sort of documented, critiqued and approved statement of work, which will (a) justify your conclusion that "there's no (longer any) good reason" to maintain two tables, and (b) clearly define the conditions (procedures and test protocols) that constitute a successful merge. Perl scripting is bound to help for actually getting the work done, once the task is fully specified.

Excellent analysis and summary. If it is the nasty case, then the suggestion to do nothing seems the right option.

It depends on what you mean by consistency. Possible ideas include the following :-

• Completely reversible - you can get out what you put in
• All exisitng queries give exactly the same answer with the new structure
• The two data structures are formally equivalent to the old one in the sense that all true (and false) statements about the original two files remain true (and false) for the new file

This is tricky to say the least, and requires a lot of consideration. My advice would be simple, unless you have to do this, don't!

use strict;     # the obligitory
use warnings;   # use strict and warnings
use Digest::MD5;

my $makedigest = \&digestit;
my %ldb = $makedigest->(\%lemployee, $lsth);
my %rdb = $makedigest->(\%remployee, $rsth);
[download]

sub digestit
{
    my $dbhash    = shift;
    my $sth       = shift;
    my %newdbhash = ();

    while ( $sth->fetchrow_arrayref )
    {
        my $keydata = Digest::MD5->new;
        for my $key ( keys %{$dbhash} )
        {
            %{$dbhash}->{$key} = ''
                unless ( defined(%{$dbhash}->{$key}) );
        }

        $keydata->add(%{$dbhash}->{$_}) for @key;

        my $digest = $keydata->hexdigest;

        $newdbhash{$digest} = {%{$dbhash}};
    }
    return %newdbhash;
}
[download]

Thanks for the advice regarding Postgres.

I'm aware that there are many issues with MySQL. But as with any tool, used appropriately, it works fine for this particular application. I perform analysis on the data and are much more dependent on small data size and raw speed than transactional behavior. MySQL has proven ideal for this.

I've personally found the Test::DatabaseRow module very helpful for testing database values.