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 ...