mySQL - Copy rows from one database to another with auto increment ids

Question

Note: Apologies if this is a duplicate but I can't find a solution.

I have two databases (one dev and one live) which have exactly the same schema.

To make things easier to explain, assume I have a 'customer' table and a 'quote' table. Both tables have auto increment ids and the quote table has a 'customerid' column that serves as a foreign key to the customer table.

My problem is that I have some rows in my dev database that I want to copy to the live database. When I copy the customer rows I can easily get a new id, but how can i get the new id to be assigned to the 'child' quote table rows?

I know I can manually script out INSERTS to overcome the problem but is there an easier way to do this?

EDIT: This is a simplified example, I have about 15 tables all of which form a hierarchy using auto-increments and foreign keys. There is considerably more data in the live database so the new ids will be bigger (e.g. dev.customer.id = 4, live.customer.id = 54)

Answer 1

Easiest way without changing any IDs.

1. Ensure that you are currently in the table where the record you want to copy is in (source db).

2. Run the following command:

INSERT INTO to_database.to_table 
  SELECT * FROM from_table WHERE some_id = 123;

No need to specify columns if there is no need to remap anything.

Hope that helps!