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.
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.
Learning & sharing
This is where we store some of our Web Development thoughts, tips and tricks for both our own uses and for others to learn from. Sharing is caring.
Please feel free to contribute to our blog posts; perhaps even teach us a few tricks of your own. We'd love to hear your thoughts.