Combining 2 tables into 1

Databases
1

I’m trying to merge 2 tables with about 1.6 mil records each into 1 table. I’m trying to find the best way to do it. So far, this is what I’ve tried:


CREATE TABLE IF NOT EXISTS `mvp_users` (
			`id` bigint(25) NOT NULL default '0',
			`mvp_number` bigint(25) default NULL,
			`fname` varchar(150) default NULL,
			`lname` varchar(150) default NULL,
			`email` varchar(150) default NULL,
			`username` varchar(100) default NULL,
			`password` varchar(32) default NULL,
			`type` varchar(255) default NULL,
			`verified` tinyint(1) default '0',
			`credit_total` int(10) default '0',
			`hash` varchar(32) default NULL,
			`address` varchar(255) default NULL,
			`address2` varchar(255) default NULL,
			`city` varchar(100) default NULL,
			`state` varchar(25) default NULL,
			`zip` varchar(12) default NULL,
			`birthdate` date default NULL,
			`sex` varbinary(25) default NULL,
			`phone` varchar(25) default NULL,
			`language` varchar(25) default NULL,
			`location` bigint(25) default NULL,
			`last_modified` timestamp NULL default NULL,
			`last_login` datetime default NULL,
			`blacklist` tinyint(1) default '0',
			`blacklisted` datetime default NULL,
			`added` timestamp NULL default CURRENT_TIMESTAMP,
			`deleted` datetime default NULL,
			PRIMARY KEY  (`id`),
			KEY `mvp_number` (`mvp_number`),
			KEY `username_email` (`username`, `email`),
			KEY `zip` (`zip`),
			KEY `location` (`location`),
			KEY `name` (`fname`,`lname`),
			KEY `lastname-search` (`lname`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO mvp_users (
	`id`,
	`mvp_number`,
	`fname`,
	`lname`,
	`email`,
	`username`,
	`password`,
	`type`,
	`verified`,
	`credit_total`,
	`hash`,
	`address`,
	`address2`,
	`city`,
	`state`,
	`zip`,
	`birthdate`,
	`sex`,
	`phone`,
	`language`,
	`location`,
	`last_modified`,
	`last_login`,
	`blacklist`,
	`blacklisted`,
	`added`
) VALUES (
	(SELECT
		u.user_id,
		u.mvp_number,
		up.pro_name,
		up.pro_last_name,
		up.pro_email,
		u.user_username,
		u.user_password,
		u.user_type,
		u.user_verified,
		u.user_credit_total,
		u.user_hash,
		up.pro_address,
		up.pro_address2,
		up.pro_city,
		up.pro_state,
		up.pro_zip,
		up.pro_bdate,
		up.pro_sex,
		up.pro_phone,
		up.pro_language,
		up.pro_loc_id,
		up.pro_last_modified,
		u.last_login,
		u.blacklist,
		u.blacklisted,
		up.pro_date
	FROM users AS u INNER JOIN user_profiles AS up ON u.user_pro_id = up.pro_id)
);

I’m pretty sure the INSERT statement is not going to work, I am getting a SQL error when i try to run this:
Error Code : 1136 - Column count doesn’t match value count at row 1

I’m assuming since the column totals match up that my select statement is not returning the right data to work with the Insert like this.

Any suggestions on how to merge 2 tables (table a and table b) into 1 table (table c)?

2

OK, I think I discovered my mistake. Here is the updated query I used:

INSERT INTO mvp_users (
    `id`,
    `mvp_number`,
    `fname`,
    `lname`,
    `email`,
    `username`,
    `password`,
    `type`,
    `verified`,
    `credit_total`,
    `hash`,
    `address`,
    `address2`,
    `city`,
    `state`,
    `zip`,
    `birthdate`,
    `sex`,
    `phone`,
    `language`,
    `location`,
    `last_modified`,
    `last_login`,
    `blacklist`,
    `blacklisted`,
    `added`
) SELECT
    u.user_id,
    u.mvp_number,
    up.pro_name,
    up.pro_last_name,
    up.pro_email,
    u.user_username,
    u.user_password,
    u.user_type,
    u.user_verified,
    u.user_credit_total,
    u.user_hash,
    up.pro_address,
    up.pro_address2,
    up.pro_city,
    up.pro_state,
    up.pro_zip,
    up.pro_bdate,
    up.pro_sex,
    up.pro_phone,
    up.pro_language,
    up.pro_loc_id,
    up.pro_last_modified,
    u.last_login,
    u.blacklist,
    u.blacklisted,
    up.pro_date
FROM users AS u INNER JOIN user_profiles AS up ON u.user_pro_id = up.pro_id;

The only question I have now is: Is this the best/fastest way? I heard an update like this may take a day or longer to run with 1.6 mil records. Any suggestions on this matter?

3

why does it matter how efficient? you’re only going to do it once, right?

:slight_smile:

4

Yes, only once, but on 3 different servers. Plus this isn’t the only operation I need to do. I have several other modifications to this database and would like this process to be as quick as possible.