Creating a Destination Table on the Fly from a Result Set

3.23.1 Problem

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

3.23.2 Solution

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

3.23.3 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 want them to appear in the destination table in the order c, a, and b, do this:

CREATE TABLE dst_tbl SELECT c, a, b FROM src_tbl;

To create additional columns in the destination table besides those selected from the source table, provide appropriate column definitions in the CREATE TABLE part of the statement. The following statement creates id as an AUTO_INCREMENT column in dst_tbl, and adds columns a, b, and c from src_tbl:

CREATE TABLE dst_tbl
(
 id INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (id)
)
SELECT a, b, c FROM src_tbl;

The resulting table contains four columns in the order id, a, b, c. Defined columns are assigned their default values. (This means that id, being an AUTO_INCREMENT column, will be assigned successive sequence numbers starting from one. See Recipe 11.2.)

If you derive a column's values from an expression, it's prudent to provide an alias to give the column a name. Suppose src_tbl contains invoice information listing items in each invoice. Then the following statement generates a summary of each invoice named in the table, along with the total cost of its items. The second column includes an alias because the default name for an expression is the expression itself, which is difficult to work with:

CREATE TABLE dst_tbl
SELECT inv_no, SUM(unit_cost*quantity) AS total_cost
FROM src_tbl
GROUP BY inv_no;

In fact, prior to MySQL 3.23.6, the alias is required, not just advisable; column naming rules are stricter and an expression is not a legal name for a column in a table.

CREATE TABLE ... SELECT is extremely convenient, but does have some limitations. These stem primarily from the fact that the information available from a result set is not as extensive as what you can specify in a CREATE TABLE statement. If you derive a table column from an expression, for example, MySQL has no idea whether or not the column should be indexed or what its default value is. If it's important to include this information in the destination table, use the following techniques:

• If you want indexes in the destination table, you can specify them explicitly. For example, if src_tbl has a PRIMARY KEY on the id column, and a multiple-column index on state and city, you can specify them for dst_tbl as well:

  CREATE TABLE dst_tbl (PRIMARY KEY (id), INDEX(state,city))
  SELECT * FROM src_tbl;

• Column attributes such as AUTO_INCREMENT and a column's default value are not copied to the destination table. To preserve these attributes, create the table, then use ALTER TABLE to apply the appropriate modifications to the column definition. For example, if src_tbl has an id column that is not only a PRIMARY KEY but an AUTO_INCREMENT column, copy the table, then modify it:

  CREATE TABLE dst_tbl (PRIMARY KEY (id)) SELECT * FROM src_tbl;
  ALTER TABLE dst_tbl MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

• If you want to make the destination table an exact copy of the source table, use the cloning technique described in Recipe 3.26.