SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining 2 tables into 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:
    Code SQL:
    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;
    Code SQL:
    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. #2
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I think I discovered my mistake. Here is the updated query I used:

    Code SQL:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by solepixel View Post
    Any suggestions on this matter?
    why does it matter how efficient? you're only going to do it once, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •