Unable to restore the users to the new database from the old database-CodePudding

This is what I try at first,

INSERT INTO db_after_deleted.users SELECT * FROM db_before_deleted.users where company_id = 30

this throws error:

#1062 - Duplicate entry '128' for key 'users.PRIMARY'

I thought may be its not allowing to use the same id, so I tried with this:

INSERT INTO db_after_deleted.users SELECT name,email,email_verified_at,password,remember_token, role_id,company_id,company_role, email_notification, email_subscription,first_time, created_at, updated_at, deleted_at FROM db_before_deleted.users where company_id = 30

This throws:

#1136 - Column count doesn't match value count at row 1

I checked and confirmed that both does have the same number of column though. How could I restore users from company_id 30. What could be the best and safest way.

CodePudding user response：

From the second error, the inserted table and source table column didn't correspond.

I would use explicitly specify the inserted columns name after INSERT INTO

INSERT INTO db_after_deleted.users
            (NAME,
             email,
             email_verified_at,
             password,
             remember_token,
             role_id,
             company_id,
             company_role,
             email_notification,
             email_subscription,
             first_time,
             created_at,
             updated_at,
             deleted_at)
SELECT NAME,
       email,
       email_verified_at,
       password,
       remember_token,
       role_id,
       company_id,
       company_role,
       email_notification,
       email_subscription,
       first_time,
       created_at,
       updated_at,
       deleted_at
FROM   db_before_deleted.users
WHERE  company_id = 30

The first error caused there are some data that duplicate exists in db_after_deleted.users, you can try to use this query to get those data then do delete them before do insert data.

SELECT *
FROM   db_before_deleted.users b
INNER JOIN db_after_deleted.users a
ON a.id = b.id
WHERE b.company_id = 30