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

I eventually managed to do this (as per my comment) but in order to do so I had to write some code. In the end I created some dummy tables that kept track of the old id against new id so. When copying over records with FK constraints I just looked up the new id based on the old. A bit long winded but it worked.

This post is getting on a bit now so I've marked this as the answer. If anyone out there has better ideas/solutions that work I'll happily 'unmark' it as the accepted answer.

EDIT: As requested here is some pseudo-code that I hope explains how I did it.

I have the two related tables as follows:

CREATE TABLE tblCustomers (
  Id int NOT NULL AUTO_INCREMENT,
  Name varchar(50) DEFAULT NULL,
  Address varchar(255) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = MYISAM
ROW_FORMAT = fixed;

CREATE TABLE tblQuotes (
  Id int NOT NULL AUTO_INCREMENT,
  CustomerId int(11) DEFAULT NULL,
  QuoteReference varchar(50) DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = MYISAM
ROW_FORMAT = fixed;

I create an extra table that I will use to track old ids against new ids

CREATE TABLE tblLookupId (
  Id int NOT NULL AUTO_INCREMENT,
  TableName varchar(50) DEFAULT NULL,
  OldId int DEFAULT NULL,
  NewId int DEFAULT NULL,
  PRIMARY KEY (Id)
)
ENGINE = MYISAM
ROW_FORMAT = fixed;

The idea is that I copy the tblCustomer rows one at a time and track the ids as I go, like this:

// copy each customer row from dev to live and track each old and new id
//
foreach (customer in tblCustomers)
{
    // track the old id
    var oldid = customer.id; // e.g. 1

    // insert the new record into the target database   
    INSERT newdb.tblCustomers (...) VALUES (...);

    // get the new id
    var newid = SELECT LAST_INSERT_ID() // e.g. 245

    // insert the old id and the new id in the id lookup table
    INSERT idlookup (TableName, OldId, NewId) VALUES ('tblCustomers', oldid, newid); // this maps 1->245 for tblCustomers
}

When I come to copy the table (tblQuote) with the foreign key I have to first lookup the new id based on the old.

// copy each quote row from dev to live and lookup the foreign key (customer) from the lookup table
//
foreach(quote in tblQuotes)
{
    // get the old foreign key value
    var oldcustomerid = quote.CustomerId; // e.g 1

    // lookup the new value
    var newcustomerid = SELECT newid FROM tblIdLookup WHERE TableName='tblCustomers' AND oldid=oldcustomerid; // returns 245

    // insert the quote record
    INSERT tblQuotes (CustomerId, ...) VALUES (newcustomerid, ...);
}

I've tried to keep this short and to the point (and language agnostic) so the technique can be seen. In my real scenario I had around 15 'cascading' tables so I had to track the new ids of every table not just tblCustomer

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!