Thoughts on Software

Creating a table directly from a SELECT statement in mySQL

with one comment

It is often useful to be able to select some data we are working with into a new table for further analysis. This is often achieved by first creating the new table and then populating it via a separate SQL statement but it can be quite time-consuming, especially when we want to work with larger numbers of columns, to write the full CREATE TABLE statement for the new table.

What would be ideal would be to be able to infer the structure of the new table directly from the SELECT statement and mySQL does actually give us a way to do that: the CREATE TABLE … SELECT statement.

Here’s an example of how it works:

CREATE TABLE my_new_working_table SELECT column_a, column_b, column_d, column_f FROM my_original_table;

And that’s it: mySQL will create the new table for you, inferring the correct structure from the SELECT statement and also insert the data that matches the SELECT. It won’t add indexes or the like but these can be added afterwards if required and when using this approach, often we only need the table for a short-term analysis task anyway.

Written by andrewlocatelliwoodcock

May 21, 2012 at 21:39

Posted in Databases, mySQL, SQL

Tagged with , ,

One Response

Subscribe to comments with RSS.

  1. If you think CREATE TABLE … SELECT is cool, check out This isn’t spam, I promise. Flexviews adds “materialized view” support to MySQL. It allows you to create a table from a resultset (like CREATE TABLE .. SELECT) but it allows you to efficiently keep that table updated based on changes to the original tables that you selected from (base tables).

    Here is a presentation about it if you want more info:

    Justin Swanhart

    January 16, 2013 at 03:42

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: