Selecting a Result Set into an Existing Table
Problem
You want to run a
SELECT query but save the results into another
table rather than displaying them.
Solution
If the other table exists, use
INSERT INTO ...
SELECT, described here. If the table
doesn’t exist, skip ahead to Recipe 3.23.
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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
