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:

Related

Категории