CODEWITHSUNDEEP
mysql
HBat
HBat

Reputation: 5682

Copying a MySQL table from one database to another where table structures differ

I want to copy the contents of a table from an old database (old_db) to a new one (new_db). The new_db.table1's structure is already set but it is empty. 

SHOW COLUMNS FROM old_db.table1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| title     | longtext    | NO   |     | NULL    |                |
| criteria  | varchar(30) | NO   |     | NULL    |                |
| threshold | smallint(6) | YES  |     | NULL    |                |
| parent_id | int(11)     | YES  | MUL | NULL    |                |
| code      | varchar(50) | NO   | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

SHOW COLUMNS FROM new_db.table1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| title     | longtext    | NO   |     | NULL    |                |
| criteria  | varchar(30) | NO   |     | NULL    |                |
| threshold | smallint(6) | YES  |     | NULL    |                |
| code      | varchar(50) | NO   | UNI | NULL    |                |
| parent_id | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

When I run:

INSERT new_db.table1 SELECT * FROM old_db.table1;

I got the following error.

ERROR 1048 (23000): Column 'code' cannot be null

I realize that it tries to copy old_db.parent_id to new_db.code due to column ordering.

I cannot drop the new_db.table1: 

DROP TABLE new_db.table1;

because it is connected to another table in new_db:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

A potential solution might be (not tried):

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE new_db.table1 MODIFY code varchar(50) NULL;
INSERT new_db.table1 SELECT * FROM old_db.table1;
UPDATE new_db.table1, old_db.table1
  SET new_db.table1.code = old_db.table1.code
  SET new_db.table1.parent_id = old_db.table1.parent_id
  where new_db.table1.id = old_db.table1.id;
ALTER TABLE new_db.table1 MODIFY code varchar(50) NOT NULL;
SET FOREIGN_KEY_CHECKS=1;

Is there a method that I can copy a table from another table in another database where table structures are different?

Upvotes: 0

Views: 30

Answers (1)

Marcus
Marcus

Reputation: 1930

What about this:

INSERT new_db.table1 
SELECT id, title, criteria, threshold, code, parent_id 
FROM old_db.table1;

Then the output of old_db.table1 has the right order for new_db.table1

Upvotes: 1

Related Questions