MySQL – INSERT SELECT Example

Feb 14th, 2010
Maruf scribbled this post.

I’m quickly going to demonstrate how to INSERT rows from one table into another table. I’d just like to clarify, this isn’t the same as completely copying a table, because with a INSERT SELECT query you can use the WHERE clause to INSERT rows with a particular condition (e.g. WHERE name = ‘Bill’). Whereas, copying an entire table will copy every single record.

Table Structure

This table is called Postcode_tbl:

INSERT SELECT Query Example

Let’s assume we have a table with the exact same structure as Postcode_tbl, but it’s called Postcode_tbl_2, and we want to copy all the rows from Postcode_tbl into Postcode_tbl_2 where the first 2 characters of the postcode is “CM”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

INSERT INTO
Postcode_tbl_2
(
postcode,
longitude,
latitude
)
SELECT
postcode,
longitude,
latitude
FROM
Postcode_tbl
WHERE
postcode LIKE 'CM%'

Now, the table structure DOESN’T need to be the same in order to use a INSERT SELECT query. I just used the same structure for ease. If the table structure differs, or the field names don’t match, just make sure you specify the correct field names and order of fields in your query.