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
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.
ON DUPLICATE KEY UPDATE might work in this case. I am not sure about the simple UPDATE as it really depends on what and how are you trying to change.
INSERT INTO db1.table SELECT * FROM db2.table;
If you want to copy data to same tables of different db.
You said "The tables and columns got different names", but you still used the same names. Try this:
INSERT INTO newDatabase.newtable1 (newColumn1, newColumn2)
SELECT oldcolumn1, oldcolumn2 FROM oldDatabase.oldtable1;
CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table
SELECT column_name FROM db1.table
