techker Posted April 2, 2016 Share Posted April 2, 2016 Hey guy's my body has a few social media site and want's to merge them all in one.i extracted all the username and emails from all DBi need to insert the username and email in the new database and once that is done take the userID an isert it in 4 other databasesso the first step is user then the others are for profiles and settings..if i don't do it the user lands on a 404 since the profile was not created..is there a way to do this automated..i inserted the usernames in Database Dump on the same server Quote Link to comment Share on other sites More sharing options...
requinix Posted April 2, 2016 Share Posted April 2, 2016 Do you know that the new users' emails are unique? They don't already exist in the database you're inserting into? If that is true then you can do this an easier way: do one mass INSERT...SELECT into the user table with the new user records, then do four more INSERT...SELECT queries where you JOIN in the new and old user tables to get the user IDs. (If that's not true then you need to resolve that regardless, but it doesn't make this much more complicated. A temporary table of just the new users would be a really quick solution.) Example? Here's the user table after you insert the new records. -- INSERT INTO users (email, username) SELECT email, username FROM old_users id | email | username ---+-------------------+--------- 1 | alice@example.com | Alice <- existing 2 | bob@example.com | Bob <- existing 7 | cindy@example.com | Cindy <- new 8 | david@example.com | David <- new7 and 8 are new. Here's the profiles table before adding new records. user_id | name | age --------+-------------+---- 1 | Alice Baker | 19 2 | Bob Smith | 25The query to add to it: INSERT INTO profiles (user_id, name, age) SELECT u.id, op.name, op.age FROM old_profiles op /* old profile data */ JOIN old_users ou ON op.user_id = ou.id /* get the email address to look up the new user record */ JOIN users u ON ou.email = u.email /* new user record has the new user ID */ Quote Link to comment Share on other sites More sharing options...
techker Posted April 2, 2016 Author Share Posted April 2, 2016 the database is new.so all the emails will be unique. i have about 1200 emails..lol so first step is insert into the user database the select user id from the database and insert into the profile and others.. but i need to add them individually..not all in one table. Quote Link to comment Share on other sites More sharing options...
benanamen Posted April 2, 2016 Share Posted April 2, 2016 (edited) If you are adding email addresses to multiple tables you're database design is wrong. Edited April 2, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
techker Posted April 2, 2016 Author Share Posted April 2, 2016 ok let me restart..sometimes i tend to right what i think in my own words..lol ok i extracted usernames and emails from databases.now we installed a new script and we want to import the username and emails in that main database.so this script it needs the main user table to have username and email a the date.once that is done user_profileuser_countuser_Activityneeds to have the id generated from user table inserted. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.