Reputation: 627
I have two databases. Now I'm trying to import all table schema only from first database to second database. I already exported all table structure from first database using phpmyadmin. But I can't import the table structures in the second database. phpmyadmin says
#1050 - Table 'XXXXXX' already exists
How can I import only the table structure correctly?
Note: Both databases had same table and all table had same structure. I have changed some table structures in the first database that I can't remember right now. Now I need to merge both table structure only. Also both database contains different data set and I can't afford any data loss from both databases.
Upvotes: 2
Views: 3212
Reputation: 4279
Before executing any command I would recommend taking full database backup, otherwise you may lost a few days of sleep.
ALTER
commandUse ALTER
command to modify table structure. Here's sql that adds age not nullable age field to users table.
ALTER TABLE users ADD age int(11) not null
I wouldn't recommend this method because you'll have data loss. Drop old table then create with new schema.
DROP TABLE mytable;
CREATE TABLE mytable (
...
);
Or if you want to keep data you can:
INSERT INTO newtable SELECT * FROM oldtable
Renaming tables might cause relationship issues. I would recommend using ALTER
command as much as possible. You can also take a look at scheme migration(aka: code first migration, database migration).
Upvotes: 2
Reputation: 627
The main Issue is merging the tables. To identify the differences between the two tables I use a small software called SQL Power Architect.
Upvotes: 0