Following in the same vain as astaines, you should (ideally) have fairly tight definitions for "merge" and "preserve consistency". What sort of arithmetic are you expecting from the merger?

• 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.

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.