MySQL Insert Select; or How To Copy Data Between Tables

SQL’s INSERT command is often a fairly simple concept; we want to insert X information into Y table, with this piece of information going into this field. It normally looks like this:

INSERT INTO people (firstname, lastname, gender)
VALUES ('The','Doctor','M')

That’s to insert a single record. If you want to insert more than one record at a time, you simply provide more value sets that are comma seperated, like so;

(firstname, lastname, gender)

For those who confuse the INSERT syntax with the UPDATE syntax, (or just plain prefer the UPDATEsyntax), MySQL extends the functionality to allow this too;

INSERT INTO people SET firsname='Martha', lastname='Jones', gender='F'

But these INSERT statements are generally for if we want to input new data – what if we want to move some data we already have from one table to another, or insert data based off existing data? This can be achieved with something called an INSERT SELECT. What it involves is all in the name – an INSERT statement, paired with a SELECT statement.

Extending the above examples, here’s an example of an INSERT SELECT that will make a quick MD5 hash based off the names of people in the people table, along with a timestamp of when the hash was created;

INSERT INTO people_hashes
id, MD5(CONCAT(firstname,surname)), NOW()
FROM people

Note: the CONCAT function was used within MD5 function to concatenate the firstname and surname fields to create a hash based off the full name – It’s got nothing to do with the INSERT SELECT

The only real requirement of an INSERT SELECT is that the information that you’re getting out from the SELECT portion of the query matches the columns you’ve defined in the INSERT portion of the query. One of the handy things about the syntax is you can run just the SELECTportion of the query first to check that it’s returning the data you want to input.

1 Reply

  1. games

    Hey very nice website!! Guy .. Excellent .. Superb .. I will bookmark your website and take the feeds additionally


Leave a Reply

Your email address will not be published. Required fields are marked *

× 4 = eight

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>