Migrate data from one database to another

Question

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

The table and column names are different and I don't have to 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 I get a #1064 - Syntax Error

What's wrong with my query and how can I fix it?

Thanks in advance

Answer 1

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

If you want to copy data to the same table in a different database.

Answer 2

Your query should look like this:

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

renew

Since this answer got more attention than I expected, I should expand on this answer. First, it might not be obvious from the answer itself, but the column names don't need to be the same. So, the following will also work (assuming the columns exist in their respective tables):

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

Also, they don't even need to be real columns in the table. One example of transforming data that I often use 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, perhaps the more obvious rule now is that as long as the SELECT query returns the same number of columns as the INSERT query requires, it can be used in place of VALUES.