P粉9856865572023-08-25 00:31:09
INSERT INTO db1.table SELECT * FROM db2.table;
If you want to copy data to the same table in a different database.
P粉6638838622023-08-25 00:15:21
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.