MySQL Insert Into from one Database in another

by

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.

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. How to select the nth row in a SQL database table?
  3. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  4. Error Code: 2013. Lost connection to MySQL server during query
  5. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  6. Storing JSON in database vs. having a new column for each key
  7. How do I restore a dump file from mysqldump?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. Adding a leading zero to some values in column in MySQL
  11. View’s SELECT contains a subquery in the FROM clause
  12. Check for x consecutive days – given timestamps in database
  13. How do I get the ID of multiple inserted rows in MySQL?
  14. How to insert data to MySQL with auto-incremented column(field)?
  15. Does mysql have the equivalent of Oracle’s “analytic functions”?
  16. Case statement in MySQL
  17. Replace Into Query Syntax
  18. Remove special characters from a database field
  19. Using reserved words in column names
  20. MySQL query, MAX() + GROUP BY
  21. How to get multiple records against one record based on relation?
  22. MySQL – Replace Character in Columns
  23. FIND_IN_SET with multiple value [duplicate]
  24. Foreign key constraints: When to use ON UPDATE and ON DELETE
  25. What is the difference between SQL and MySQL? [closed]
  26. How to use a user variables in MySQL LIKE clause?
  27. Selecting distinct 2 columns combination in mysql
  28. How to store a one to many relation in MySQL database?
  29. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  30. MySQL: Computed Column

Leave a Comment