How to merge two databases, both with primary keys I have two databases, both with the same tables, but the data is out of sync.

I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a new ID.

E.G

Table users in B has a row with ID (auto increment, primary) 5, for a guy called Bob. Table users in A has a record for a guy called Dave in row ID 5.

I'd like Dave to be added to B.users but with a new ID.

I have looked at using Navicats Data Sync, but there are no advanced options to allow this, only to stop it overwriting (updating) or removing the record.

What are my options here? Is it a manual job?
I assume there is a UNIQUE index on the column with "Dave" and "Bob"?  Without that, how do you know what is "new"?

Let's call the column name (distinct from id).

Does some other table depend on Dave=5?  If so, the following will not suffice, and the solution is much messier.

Are there changes to the columns?  Or is it only "new" rows to be copied from A to B?

INSERT INTO B.user
    SELECT ...  -- excluding `id`
        FROM A.user
        LEFT JOIN B.user  USING(email)
        WHERE B.id IS NULL;     -- Not currently in B


To handle changes, look at INSERT ... ON DUPLICATE KEY UPDATE, again with a JOIN, but this time IS NOT NULL.