I have two separate schema with same structure of tables in mysql, so planning to insert values from schema A table to schema b table.

Error : INSERT INTO B.woreemp (id, created_at, designation, expertise, name) VALUES (SELECT employees.id, employees.created_at, employees.designation, employees.expertise, employees.name FROM world.employees) Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT employees.id, employees.created_at, employees.designation, em' at line 8 0.000 sec

Here is sql query.

INSERT INTO B.woreemp (id, created_at, designation, expertise, name) VALUES (SELECT employees.id,
employees.created_at,
employees.designation,
employees.expertise,
employees.name FROM A.employees);

Please advise

1 Answers

3
lisu On Best Solutions

You have to remove VALUES from your query, this should work:

INSERT INTO B.woreemp (id, created_at, designation, expertise, name) 
    SELECT 
        employees.id,
        employees.created_at,
        employees.designation,
        employees.expertise,
        employees.name 
    FROM A.employees;

Depending on the schema of the table, you might not be able to do this (if id in B.woreemp.id is autoincrement or something similar). In that case just not populate that column and remove it from both column lists.

You can find more details here: http://www.mysqltutorial.org/mysql-insert-into-select/