MySQL merge tables with different structure

Question

I have two databases with different structure.

Table 1:

ch_code    ch_def    ch_weight

Table 2:

address    ch_code

I need to merge this two tables, so the structure would look like:

ch_code    ch_def    ch_weight    address

The number or rows in two tables are different (table 1 has more data).

Should I use merge, union.. something else?

Thank you!

Answer 1

If Table2 only has data that's included in Table1 (i.e. there's nothing in Table2 that's not in Table1), you should be able to do something like (assuming Table3 has been setup already):

INSERT INTO TABLE3 (ch_code, ch_def, ch_weight, address)
SELECT Table1.ch_code, Table1.ch_def, Table1.ch_weight, Table2.address
FROM Table1 LEFT JOIN Table2 on Table1.ch_code = Table2.ch_code

(I don't have a MySQL install handy, so your specific syntax my vary.)

If you have data in Table2 that doesn't match data in Table1 (and you want to keep that data), you'll need a FULL JOIN (if MySQL doesn't support that, UNION a LEFT JOIN and RIGHT JOIN).

Answer 2

Here's a solution which should handle the three possible cases:

1. There's a ch_code value in t1 that's not in t2
2. There's a ch_code value in t2 that's not in t1
3. There's a ch_code value in both t1 and t2

SELECT t1.ch_code, t1.ch_def, t1.ch_weight, '' as address from t1 where not exists (select * from t2 where t2.ch_code = t1.ch_code)

UNION

SELECT t2.ch_code, '' as ch_def, '' as ch_weight, t2.address from t2 where not exists (select * from t1 where t1.ch_code = t2.ch_code)

UNION

SELECT t1.ch_code, t1.ch_def, t1.ch_weight, t2.ch.address from t1 left join t2 on t1.ch_code = t2.ch_code

Once you've obtained that resultset then you may do your INSERT INTO if you have a new table for housing the merged data.