search

Home  >  Q&A  >  body text

Migrate data from one database to another

<p>I need to migrate data from one database to another, both on the same local system. </p> <p>The table and column names are different and I don't have to migrate all the columns from the old database, so </p> <p><code>Select *</code> doesn't work for me. </p> <pre class="brush:php;toolbar:false;">INSERT INTO newDatabase.table1(Column1, Column2); SELECT oldDatabase.table1(column1, column2) FROM oldDatabase.table1</pre> <p>But I get a <code>#1064 - Syntax Error</code></p> <p>What's wrong with my query and how can I fix it? </p> <p>Thanks in advance</p>
P粉509383150P粉509383150460 days ago660

reply all(2)I'll reply

  • P粉985686557

    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.

    reply
    0
  • P粉663883862

    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.

    reply
    0
  • Cancelreply