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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.