MySQL Cookbook

Creating a Destination Table on the Fly from a Result Set

Problem

You want to run a SELECT query and save the result set into another table, but that table doesn’t exist yet.

Solution

Create the destination table first, or create it directly from the result of the SELECT.

Discussion

If the destination table does not exist, you can create it first with a CREATE TABLE statement, then copy rows into it with INSERT ... SELECT as described in Recipe 3.22. This technique works for any version of MySQL.

In MySQL 3.23 and up, a second option is to use CREATE TABLE ... SELECT, which creates the destination table directly from the result of a SELECT. For example, to create dst_tbl and copy the entire contents of src_tbl into it, do this:

CREATE TABLE dst_tbl SELECT * FROM src_tbl;

MySQL creates the columns in dst_tbl based on the name, number, and type of the columns in src_tbl. Add an appropriate WHERE clause, should you wish to copy only certain rows. If you want to create an empty table, use a WHERE clause that is always false:

CREATE TABLE dst_tbl SELECT * FROM src_tbl WHERE 0;

To copy only some of the columns, name the ones you want in the SELECT part of the statement. For example, if src_tbl contains columns a, b, c, and d, you can copy just b and d like this:

CREATE TABLE dst_tbl SELECT b, d FROM src_tbl;

To create columns in a different order than that in which they appear in the source table, just name them in the desired order. If the source table contains columns a, b, and c, but you ...