This may seem a bit off-topic, but as I'm thinking perl when I try to find a way to solve this, here's the question:

I have two (MySQL) database tables containing financial transaction records. I wish to merge them into a single table, mainly for ease of use and maintenance reasons, but also since there's no (longer any) good reason this information should be split into two tables at all. There are about 40M records in each table. I need to be able to determine that the conversion preserved consistency, i.e. that the information logically remains exactly the same.

Has anybody faced similar challenges? I'm thinking in terms of script(s) involving Test::Harness which would magically spit out ok/nok would be very nice. 

-- Two original tables
create table a (
  id int(11) unsigned not null,
  account_no bigint(26) unsigned not null,
  unique key id_idx (id)
);
create table b (
  id int(11) unsigned not null,
  balance double not null,
  unique key id_idx (id)
);
-- New table
create table c (
  id int(11) unsigned not null,
  account_no bigint(26) not null,
  balance float not null,
  unique key id_idx (id)
);
insert into a values (1,1);
insert into a values (2,2);
insert into b values (1,100.0);
insert into b values (2,200.0);

-- Populate c with values from a and b
insert into c (
  id,
  account_no,
  balance) 
select
  a.id,
  a.account_no,
  b.balance
from a, b
where a.id=b.id;

select * from c;
+----+------------+---------+
| id | account_no | balance |
+----+------------+---------+
|  1 |          1 |     100 |
|  2 |          2 |     200 |
+----+------------+---------+
2 rows in set (0.00 sec)

[download]
By consistency I mean that for every id the queries below should return the same result set. 
select a.id, a.account_no, b.balance from a, b where a.id=b.id;
select id, account_no, balance from c where id=X;

[download]
Do you still consider advice to leave it as is? Andreas

In reply to Verify database consistency after merge by andreas1234567

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.