Hi, everyone,

I am wondering if anyone could let me know how I can replace an entire table structure for another one? I am trying to upload a backup file into a new database but the structures are different, so it won't work.

Here is an example:

I want to replace this table:
`members` (
`id` mediumint(8) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`mgroup` smallint(3) NOT NULL default '0',
`legacy_password` varchar(32) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`joined` int(10) NOT NULL default '0',
`ip_address` varchar(16) NOT NULL default '',
`posts` mediumint(7) default '0',
`title` varchar(64) default NULL,
`allow_admin_mails` tinyint(1) default NULL,
`time_offset` varchar(10) default NULL,
`hide_email` varchar(8) default NULL,
`email_pm` tinyint(1) default NULL,
`email_full` tinyint(1) default NULL,
`skin` smallint(5) default NULL,
`warn_level` int(10) default NULL,
`warn_lastwarn` int(10) NOT NULL default '0',
`language` varchar(32) default NULL,
`last_post` int(10) default NULL,
`restrict_post` varchar(100) NOT NULL default '0',
`view_sigs` tinyint(1) default '1',
`view_img` tinyint(1) default '1',
`view_avs` tinyint(1) default '1',
`view_pop` tinyint(1) default '1',
`bday_day` int(2) default NULL,
`bday_month` int(2) default NULL,
`bday_year` int(4) default NULL,
`new_msg` tinyint(2) default NULL,
`msg_total` smallint(5) default '0',
`show_popup` tinyint(1) default NULL,
`misc` varchar(128) default NULL,
`last_visit` int(10) default '0',
`last_activity` int(10) default '0',
`dst_in_use` tinyint(1) default '0',
`view_prefs` varchar(64) default '-1&-1',
`coppa_user` tinyint(1) default '0',
`mod_posts` varchar(100) NOT NULL default '0',
`auto_track` varchar(50) default '0',
`org_perm_id` varchar(255) default '',
`temp_ban` varchar(100) default '0',
`login_anonymous` char(3) NOT NULL default '0&0',
`ignored_users` text NOT NULL,
`mgroup_others` varchar(255) NOT NULL default '',
`member_login_key` varchar(32) NOT NULL default '',
`subs_pkg_chosen` smallint(3) NOT NULL default '0',
`sub_end` int(10) NOT NULL default '0',
`map` varchar(100) default NULL,
`map_location` varchar(15) default NULL,
`map_coords` varchar(30) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `mgroup` (`mgroup`),
KEY `bday_day` (`bday_day`),
KEY `bday_month` (`bday_month`)
) TYPE=MyISAM;



For this one:

`members` (
`id` mediumint(8) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`mgroup` smallint(3) NOT NULL default '0',
`email` varchar(150) NOT NULL default '',
`joined` int(10) NOT NULL default '0',
`ip_address` varchar(16) NOT NULL default '',
`posts` mediumint(7) default '0',
`title` varchar(64) default NULL,
`allow_admin_mails` tinyint(1) default NULL,
`time_offset` varchar(10) default NULL,
`hide_email` varchar(8) default NULL,
`email_pm` tinyint(1) default '1',
`email_full` tinyint(1) default NULL,
`skin` smallint(5) default NULL,
`warn_level` int(10) default NULL,
`warn_lastwarn` int(10) NOT NULL default '0',
`language` varchar(32) default NULL,
`last_post` int(10) default NULL,
`restrict_post` varchar(100) NOT NULL default '0',
`view_sigs` tinyint(1) default '1',
`view_img` tinyint(1) default '1',
`view_avs` tinyint(1) default '1',
`view_pop` tinyint(1) default '1',
`bday_day` int(2) default NULL,
`bday_month` int(2) default NULL,
`bday_year` int(4) default NULL,
`new_msg` tinyint(2) default '0',
`msg_total` smallint(5) default '0',
`show_popup` tinyint(1) default '0',
`misc` varchar(128) default NULL,
`last_visit` int(10) default '0',
`last_activity` int(10) default '0',
`dst_in_use` tinyint(1) default '0',
`view_prefs` varchar(64) default '-1&-1',
`coppa_user` tinyint(1) default '0',
`mod_posts` varchar(100) NOT NULL default '0',
`auto_track` varchar(50) default '0',
`temp_ban` varchar(100) default '0',
`sub_end` int(10) NOT NULL default '0',
`login_anonymous` varchar(3) NOT NULL default '0&0',
`ignored_users` text,
`mgroup_others` varchar(255) NOT NULL default '',
`org_perm_id` varchar(255) NOT NULL default '',
`member_login_key` varchar(32) NOT NULL default '',
`member_login_key_expire` int(10) NOT NULL default '0',
`subs_pkg_chosen` smallint(3) NOT NULL default '0',
`has_blog` tinyint(1) NOT NULL default '0',
`has_gallery` tinyint(1) NOT NULL default '0',
`members_markers` text,
`members_editor_choice` varchar(3) NOT NULL default 'std',
`members_auto_dst` tinyint(1) NOT NULL default '1',
`members_display_name` varchar(255) NOT NULL default '',
`members_created_remote` tinyint(1) NOT NULL default '0',
`members_cache` mediumtext,
`members_disable_pm` int(1) NOT NULL default '0',
`members_l_display_name` varchar(255) NOT NULL default '0',
`members_l_username` varchar(255) NOT NULL default '0',
`failed_logins` text,
`failed_login_count` smallint(3) NOT NULL default '0',
`members_profile_views` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `members_l_display_name` (`members_l_display_name`),
KEY `members_l_username` (`members_l_username`),
KEY `mgroup` (`mgroup`),
KEY `bday_day` (`bday_day`),
KEY `bday_month` (`bday_month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

this new table, will it load its own data rows?

in other words, do you need the data in the old table?

Yes, I definitely need to the old data... It is a Forum with nearly 100.000 posts and over 2.000 members.

do you know the column-to-column mapping that is required?

you will do this in steps --

CREATE TABLE newtable ( ... )

INSERT INTO newtable ( columns )
SELECT columns or expressions
FROM oldtable

Hmmmm, I'm not sure I understand... I already have the new table. I just need to insert the data from the old one, but it is not compatible because the new one has different values. It also looks like the order of those values matter. If you want, I can send you the files for both tables.

no, please don't do that

have you ever seen INSERT SELECT syntax? do you know what's necessary?

list the columns of the receiving table, and then write the SELECT to match column for column

for example

INSERT INTO newtable ( name, hatsize, IQ )
SELECT username, 7.5, 145 from users

so you would list every column in your new table and match that with whatever columns from your old table, with constants and/or defaults and/or NULLs as necessary

I know the INSERT command, but not the INSERT SELECT one. I will give it a try and see if I understand it. I'll let you know if I managed. Thanks.