Home > Uncategorized > Quick Primer: SQL: Migrate a table to a table with a modified structure

Quick Primer: SQL: Migrate a table to a table with a modified structure

1) Create a new table with the structure you want (the new structure):

CREATE TABLE  `redmine`.`new_enumerations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `position` int(11) DEFAULT '1',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `type` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `project_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_enumerations_on_project_id` (`project_id`),
  KEY `index_enumerations_on_id_and_type` (`id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2) Take the records from the old table and insert them into the new table. Note that each field/column name that is listed is present in the old table and the new table. If fields/columns are removed in either, then there obviously isn’t a place for the info. It might be stored somewhere else. Investigate.

INSERT new_enumerations (`id`,`name`,`position`,`is_default`) SELECT `id`,`name`,`position`,`is_default` FROM enumerations

3) Delete the old table because you don’t need it any more.

DROP TABLE IF EXISTS `redmine`.`enumerations`;

4) Rename the new table to the production table name:

ALTER TABLE `redmine`.`new_enumerations` RENAME `redmine`.`enumerations`;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: