Insert missing records from one table to another using mysql

Question

I don't know why I am confused with this query.

I have two table: Table A with 900 records and Table B with 800 records. Both table need to contain the same data but there is some mismatch.

I need to write a mysql query to insert missing 100 records from Table A to Table B.

In the end, both Table A and Table B should be identical.

I do not want to truncate all the entries first and then do a insert from another table. So please any help is appreciated.

Thank you.

Answer 1

It is also possible to use LEFT OUTER JOIN for that. This will avoid subquery overhead (when system might execute subquery one time for each record of outer query) like in John Woo's answer, and will avoid doing unnecessary work overwriting already existing 800 records like in user2340435's one:

INSERT INTO b
SELECT a.* FROM a
LEFT OUTER JOIN b ON b.id = a.id
WHERE b.id IS NULL;

This will first select all rows from A and B tables including all columns from both tables, but for rows which exist in A and don't exist in B all columns for B table will be NULL. Then it filter only such latter rows (WHERE b.id IS NULL), and at last it inserts all these rows into B table.

Answer 2

I think you can use IN for this. (this is a simpliplification of your query)

INSERT INTO table2 (id, name)
SELECT id, name
FROM table1
WHERE (id,name) NOT IN 
       (SELECT id, name
        FROM table2);

SQLFiddle Demo

AS you can see on the demonstration, table2 has only 1 records but after executing the query, 2 records were inserted on table2.