0

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.