CODEWITHSUNDEEP
mysqldatabasedatabase-schema
Empty Brain
Empty Brain

Reputation: 627

Is there any way to import database table schema only

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

Answers (2)

TheMisir
TheMisir

Reputation: 4279

Before executing any command I would recommend taking full database backup, otherwise you may lost a few days of sleep.

Using ALTER command

Use 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

Re-creating table

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:

  1. Duplicate or rename table to different name.
  2. Create a new table with new schema.
  3. Copy data from old table: 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

Empty Brain
Empty Brain

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

Related Questions