Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases (db1 and db2). db2 has all tables from db1 but not necessarily the same columns. I want to move any missing columns in db2 from tables in db1 but keep the rest unchanged.

example:

db1 schema for table 'account'

CREATE TABLE IF NOT EXISTS `account` (
  `ListID` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `ParentRef_ListID` varchar(255) DEFAULT NULL,
  `ParentRef_FullName` varchar(255) DEFAULT NULL,
  `CurrencyRef_ListID` varchar(255) DEFAULT NULL,
  `CurrencyRef_FullName` varchar(255) DEFAULT NULL,
  `CustomField1` varchar(50) DEFAULT NULL,
  `CustomField2` varchar(50) DEFAULT NULL,
  `CustomField3` varchar(50) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

db2 schema for table 'account':

CREATE TABLE IF NOT EXISTS `account` (
  `qbsql_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ListID` varchar(255) DEFAULT NULL,
  `TimeCreated` varchar(255) DEFAULT NULL,
  `TimeModified` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

final schema for table 'account':

CREATE TABLE IF NOT EXISTS `account` (
  `qbsql_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ListID` varchar(255) DEFAULT NULL,
  `TimeCreated` varchar(255) DEFAULT NULL,
  `TimeModified` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `ParentRef_ListID` varchar(255) DEFAULT NULL,
  `ParentRef_FullName` varchar(255) DEFAULT NULL,
  `CurrencyRef_ListID` varchar(255) DEFAULT NULL,
  `CurrencyRef_FullName` varchar(255) DEFAULT NULL,
  `CustomField1` varchar(50) DEFAULT NULL,
  `CustomField2` varchar(50) DEFAULT NULL,
  `CustomField3` varchar(50) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The databases have over 200 tables in them so a "single table at a time" solution will not work for me here.

share|improve this question
    
Are you wanting to merge data? Or just declare new columns on tables in db2 so that the columns defined on the same table name in db1 are also added on the table in db2? –  Michael - sqlbot Dec 18 '13 at 5:18
    
Not worried about data. –  frankV Dec 18 '13 at 6:41
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.