mysql - SELECT AND INSERT QUERY into multiple table with conditions

Question

I dont know it is possible or not in MySQl, but I have to import the old database table to new normalized database table. I have 2 table user and user_roles in new one and the previous one only has admin

newdb.users Table
user_id | username | password

newdb.user_roles Table
user_id | role_id

old database table

olddb.admin Table
id | username | password | user_type
1  | user1    | ***      | admin
3  | user7    | ***      | staff

admin role_id is 1 and staff role_id is 2

So the new table after SQL insert should look like.

newdb.users Table
user_id | username | password
1       | user1    | ***   
3       | user7    | ***   

newdb.user_roles Table
user_id | role_id
1       |  1
3       |  2

How to do this using MYSQL query. Thanks.

score 2 · Accepted Answer · answered Aug 06 '16 at 13:34

You should be basing this information on a roles table:

create table roles (
    int id auto_increment primary key,
    role_name varchar(255)
);

insert into roles (role_name)
    select distinct user_type
    from old.admin;

Then:

insert into new.users(username, password)
    select distinct username, password
    from old.admin;

(distinct is not necessary if username is not repeated in original table.)

And:

insert into user_roles(user_id, role_id)
    select u.id, r.id
    from new.users u join
         old.admin a
         on u.user_name = a.user_name and
            u.password = a.password join  -- this condition may not be necessary
         new.roles r
         on a.user_type = r.role_name;

Although you can hard-code values into the query, I think it is a good idea to let the database do the work for you. This approach is more general.

Thanks @gordon, it is working perfectly, and Just to make thing simple i did't mentioned about the roles table i too had that. Just one thing you miss type username as user_name. anyway thank you very much. — Raunak Gupta, Aug 08 '16 at 08:12

score 0 · Answer 2 · answered Aug 06 '16 at 13:26

In order to insert roles from old table to new table:

INSERT INTO newdb.user_roles 

SELECT 
id,
CASE WHEN user_type='admin' THEN 1
     WHEN user_type ='staff' THEN 2 END AS role_id
FROM olddb.admin

In order to insert users from old table to new table:

INSERT INTO newdb.users
SELECT 
id,
username,
password
FROM olddb.admin

mysql - SELECT AND INSERT QUERY into multiple table with conditions

2 Answers2