HOWTO: MySQL - Insert Data from one table to another table

I have just been refactoring a legacy database and needed to copy some data from one table into a new table in MySQL.

Very easy task but very easily forgotten, just thought I would blog about it to help anyone searching for a quick answer…

Syntax:

INSERT INTO tablename (colomn1, colomn2 ...)
SELECT colomn1, colomn2 ...
FROM tablename;

For example

If i had a products table and needed to copy the product title into another table named ‘merchandise’ I would simply:

INSERT INTO merchandise (title)
SELECT title FROM products;

Tags: ,

This entry was posted on Tuesday, November 18th, 2008 at 12:11 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Responses

There have been 12 responses to HOWTO: MySQL - Insert Data from one table to another table.

  1. Comment made by Shaun on
    November 20th, 2008 at 9:43 am

    Very helpful, worked great!

  2. Comment made by Phil Thompson on
    November 21st, 2008 at 6:33 pm

    Nice action there: I feel like this is something I probably should’ve known about already but, shamefully didn’t!

  3. Comment made by mark-holt on
    November 21st, 2008 at 8:06 pm

    @Shaun - glad it helped

    @Phil - yeah its something you tend to not use all the time, but when you need to do it you think- “how do i do that”. So I thought I would blog to remind people how easy it really is!

  4. Comment made by Jerald on
    December 8th, 2008 at 11:25 am

    Superb…

    i found it.. from you.. Thanks

  5. Comment made by Zydre on
    March 21st, 2009 at 2:26 pm

    maybe someone know, how to insert data in one table, but that others tables don’t see it?

  6. Comment made by sitnefestus on
    March 25th, 2009 at 11:20 am

    Thanks you. Verry quick and helpfull.

  7. Comment made by suresh on
    October 10th, 2009 at 3:08 am

    Thanks a lot boss and i need ur tips(quick answer) continuously…

  8. Comment made by Andy Potter on
    October 16th, 2009 at 10:45 am

    Thanks, just what I was looking for

  9. Comment made by Dast on
    October 29th, 2009 at 9:10 pm

    Hi,
    Nearly what I was looking for(!) - but how can I insert a mixture of data from another table with new values? E.g. column1 FROM table2 but column2 =”new value”
    Is this possible? Thanks

  10. Comment made by mirrors on
    November 5th, 2009 at 9:04 pm

    Thanks so simple yet I forgot.

  11. Comment made by Bridget on
    November 29th, 2009 at 8:02 pm

    Hi there,

    I’m using ColdFusion, Dreamweaver and MYSQL. I have a table with a user ID, when the user adds a comment I want their User ID to come up in the ‘addcomment’ table. I’ve tried adding this code you wrote - but it’s still not working.. and idea’s why not?

  12. Comment made by mark-holt on
    November 29th, 2009 at 9:35 pm

    hi bridget, please send over some code so I can take a look?

Leave a Reply

Commenting rules

  • Try to keep your comments as relevant as possible.
  • No HTML/JavaScript/BBcode.
  • Don't be abusive: No racism, homophobia or any other nastiness.
  • Feel free to express your opinion, but do so in an eloquent way.

If you do not respect these rules your comments may be edited or even deleted.