MySQL Cookbook, 2nd Edition

Saving a Query Result in a Table

Problem

You want to save the result from a SELECT statement into a table rather than display it.

Solution

If the table already exists, just use INSERT INTO ... SELECT to retrieve rows into it. If the table does not exist yet, use CREATE TABLE ... SELECT to create it on the fly from the SELECT result.

Discussion

The MySQL server normally returns the result of a SELECT statement to the client that issued the statement. For example, when you issue a statement from within the mysql program, the server returns the result to mysql, which in turn displays it to you on the screen. It’s also possible to save the results of a SELECT statement in a table, which is useful in a number of ways:

• You can easily create a complete or partial copy of a table. If you’re developing an algorithm that modifies a table, it’s safer to work with a copy of a table so that you need not worry about the consequences of mistakes. Also, if the original table is large, creating a partial copy can speed the development process because queries run against it will take less time.

• For a data-loading operation based on information that might be malformed, you can load new rows into a temporary table, perform some preliminary checks, and correct the rows as necessary. When you’re satisfied that the new rows are okay, copy them from the temporary table into your main table.

• Some applications maintain a large repository table and a smaller working table into which rows are inserted on a regular basis, copying the working table rows to the repository periodically and clearing the working table.

• If you’re performing a number of similar summary operations on a large table, it may be more efficient to select summary information once into a second table and use that for further analysis, rather than run expensive summary operations repeatedly on the original table.

This section shows how to retrieve a result set into a table. The table names src_tbl and dst_tbl in the examples refer to the source table from which rows are selected and the destination table into which they are stored, respectively.

If the destination table already exists, use INSERT ... SELECT to copy the result set into it. For example, if dst_tbl contains an integer column i and a string column s, the following statement copies rows from src_tbl into dst_tbl, assigning column val to i and column name to s:

INSERT INTO dst_tbl (i, s) SELECT val, name FROM src_tbl;

The number of columns to be inserted must match the number of selected columns, and the correspondence between sets of columns is established by position rather than name. In the special case that you want to copy all columns from one table to another, you can shorten the statement to this form:

INSERT INTO dst_tbl SELECT * FROM src_tbl;

To copy only certain rows, add a WHERE clause that selects those rows:

INSERT INTO dst_tbl SELECT * FROM src_tbl
WHERE val > 100 AND name LIKE 'A%';

The SELECT statement can produce values from expressions, too. For example, the following statement counts the number of times each name occurs in src_tbl and stores both the counts and the names in dst_tbl:

INSERT INTO dst_tbl (i, s) SELECT COUNT(*), name
FROM src_tbl GROUP BY name;

If the destination table does not exist, you can create it first with a CREATE TABLE statement, and then copy rows into it with INSERT ... SELECT. A second option is to use CREATE TABLE ... SELECT, which creates the destination table directly from the result of the 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. Should you want to copy only certain rows, add an appropriate WHERE clause. 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 from that in which they appear in the source table, 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 Creating a Sequence Column and Generating Sequence Values.)

If you derive a column’s values from an expression, it’s prudent to provide an alias to give the column a name. Suppose that src_tbl contains invoice information listing items in each invoice. The following statement then 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 a difficult name to work with later:

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

CREATETABLE ... SELECT is extremely convenient, but it 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 CREATETABLE statement. If you derive a table column from an expression, for example, MySQL has no idea whether 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:

• To make the destination table an exact copy of the source table, use the cloning technique described in Cloning a Table.

• 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, and 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, and 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;