MySQL - copy rows from one database to another using auto increment ids

Question

Note. Sorry if this is a duplicate, but I cannot find a solution.

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

To simplify the explanation, suppose I have a customer table and a quote table. Both tables have auto-increment identifiers, and the quote table has a customerid column that serves as a foreign key for the customer table.

My problem is that I have some rows in my dev database that I want to copy to a live database. When I copy client rows, I can easily get a new identifier, but how can I get a new identifier to bind to the rows of the "child" table?

I know that I can manually script from 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 automatic increments and foreign keys. There is much more data in the live database, so there will be more new identifiers (for example, dev.customer.id = 4, live.customer.id = 54)

Answer 1

In the end, I managed to do this (according to my comment), but for this I had to write code. In the end, I created some dummy tables that tracked the old id from the new id. When copying records with FK restrictions, I simply looked for a new identifier based on the old one. A little long, but it worked.

This post is getting a little messy, so I marked this as an answer. If someone has the best ideas / solutions that work, I will gladly “cancel” this as an accepted answer.

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

I have two related tables:

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 am creating an additional table that I will use to track old identifiers against new identifiers

 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 lines one at a time and keep track of the identifiers when I go, for example:

 // 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; // eg 1 // insert the new record into the target database INSERT newdb.tblCustomers (...) VALUES (...); // get the new id var newid = SELECT LAST_INSERT_ID() // eg 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 a table (tblQuote) using a foreign key, I need to first find a new identifier based on the old one.

 // 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; // eg 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 tried to keep this short and precise (and linguistic agnostic) so that the technique could be seen. In my real scenario, I had about 15 cascading tables, so I had to track the new identifiers of each table, not only tblCustomer

Answer 2

easiest without changing identifiers

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

there is no need to specify columns, unless there is a need to reassign something.

Hope this helps ...

Answer 3

Use INSERT ... SELECT :

 insert into your_table (c1, c2, ...) select c1, c2, ... from your_table 

where c1, c2, ... are all columns except id .