MySQL Insert Into from one Database in another

Question

I need to migrate data from one Database to another one, both are on the same local system.

The tables and columns got different names and I mustn't migrate all the Columns from the old Database, so

Select * doesn't work for me.

INSERT INTO newDatabase.table1(Column1, Column2); SELECT oldDatabase.table1(column1, column2) FROM oldDatabase.table1 

but all i got is a #1064 - Syntax Error

What is the error in my Query and How can i fix this ?

Thanks in advance

Answer 1

Your query should go like this:

INSERT INTO newDatabase.table1 (Column1, Column2)  SELECT column1, column2 FROM oldDatabase.table1; 

UPDATE

Since this answer is getting more attention than I even anticipated, I should expand on this answer. First of all, it might not be obvious from the answer itself, but the columns do not need to have the same name. So, following will work too (assuming that the columns exist in their respective tables):

INSERT INTO newDatabase.table1 (Column1, Column2)  SELECT SomeOtherColumn, MoreColumns FROM oldDatabase.table1; 

Furthermore, they don't even need to be real columns in the table. One of the examples for transforming data that I use quite often is:

INSERT INTO newDatabase.users (name, city, email, username, added_by)  SELECT CONCAT(first_name, ' ', last_name), 'Asgard', CONCAT(first_name,'@gmail.com'), CONCAT(first_name,last_name), 'Damir' FROM oldDatabase.old_users; 

So, as it might be more obvious now, the rule is, as long as the SELECT query returns same number of columns that INSERT query needs, it can be used in place of VALUES.

Answer 2

INSERT INTO db1.table SELECT * FROM db2.table; 

If you want to copy data to same tables of different db.