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 ...