This is something I occasionally want to do, but I have usually forgotten how to do it, so the recipe goes here on the back of my giant online envelope.
There are two ways to merge two tables. One involves using a union clause. This way the tables stay separate, but can be handled as one in any searches or data munging.
The other way is to actually modify one of the tables. This has at least one feature that a union clause does not: it can produce a unique id number for all the records in the merged table. This is something I often want, so today’s recipe covers how to do that.
The main reason that I have problems with a merge is because I use auto-increment to produce a unique key for each record. Call this the primary key. Of course when you merge two tables produced this way, their primary keys clash.
Assume that the tables have identical structure. Call the table that you want to hold the result of the merge table A. Call the table you want to merge into A table B. Call the primary key on both tables id. To merge B into A:
1) create table btemp select * from b (create temp table with no primary index)
2) update Btemp set id = 0 (This is okay because temp table has no primary index)
3) insert into A select * from btemp
mysql will insert all records from b, continuing with the auto-inc numbering for the primary key a.id