Compare unique values from two mysql tables

Question

I have two mysql tables: TableA has 10,000 records TableB has 2,000 records.

I want to copy the 8,000 unique records from TableA into TableB ignoring the 2,000 in TableB which have already been copied.

Answer 1

If uniqueness is determined by PRIMARY KEY constraint or UNIQUE constraint, then you can use INSERT IGNORE:

INSERT IGNORE INTO TableB SELECT * FROM TableA;

The rows that are duplicates and that conflict with rows already in TableB will be silently skipped and the other 8,000 rows should be inserted.

See the docs on INSERT for more details.

If you need to do this in PHP, read about the array_diff_key() function. Store your arrays with the primary key values as the key of the array elements. No guarantees for the performance of this PHP function on such large arrays, though!

Answer 2

Use the INSERT INTO syntax:

INSERT INTO TABLE_B 
  SELECT *
    FROM TABLE_A a
   WHERE NOT EXISTS(SELECT NULL
                      FROM TABLE_B b
                     WHERE b.column = a.column)

You'll need to update the WHERE b.column = a.column) to satisfy however you determine that a record already exists in TABLE_B.

Answer 3

What about something like this :

insert into TableB
select *
from Table A
where not exists (
    select 1
    from TableB
    where TableB.id = TableA.id
)

Or, if the entries in table B are "not unique" because of their primary key, an insert ignore might do the trick, I suppose.