MySQL Cookbook

3.22 Selecting a Result Set into an Existing Table

3.22.1 Problem

You want to run a SELECT query but save the results into another table rather than displaying them.

3.22.2 Solution

If the other table exists, use INSERT INTO ... SELECT, described here. If the table doesn't exist, skip ahead to Recipe 3.23.

3.22.3 Discussion

The MySQL server normally returns the result of a SELECT statement to the client that issued the statement. For example, when you run a query from within mysql, the server returns the result to mysql, which in turn displays it to you on the screen. It's also possible to send the results of a SELECT statement directly into another table. Copying records from one table to another is useful in a number of ways:

• 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 data-loading operations that work with information that might be malformed, you can load new records into a temporary table, perform some preliminary checks, and correct the records as necessary. When you're satisfied the new records 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 records are inserted on a regular basis, copying the working table records 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 running expensive summary operations repeatedly on the original table.

This section shows how to use INSERT ... SELECT to retrieve a result set for insertion into an existing table. The next section discusses CREATE TABLE ... SELECT, a statement available as of MySQL 3.23 that allows you to create a table on the fly directly from a query result. 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.

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 the rows you want:

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

It's not necessary to copy column values without modification from the source table into the destination table. The SELECT statement can produce values from expressions, too. For example, the following query 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;

When you use INSERT ... SELECT, you cannot use the same table both as a source and a destination.