How to save MySQL query results in a new table

In MySQL, in one SQL query, you can create a new table and store in it MySQL query results (search results of another table). Or you can save the MySQL query results to another, already existing table.

How to store query results in a new table in MySQL

Use the following construct:

CREATE TABLE new_table SELECT //search terms go here

Example:

CREATE TABLE `db2`.`new_table` SELECT `column` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column` DESC;

Note that the part that starts with SELECT is in syntax a normal SELECT search query. That is, you can test your query without creating a new table:

SELECT `column` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column` DESC;

And if it worked correctly, then add this query after “CREATE TABLE new_table”.

How to save MySQL query results with multiple columns to a new table

If you want multiple columns to be stored in the new table, then list them after the SELECT:

CREATE TABLE `db2`.`new_table` SELECT `column1` , `column2` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column1` DESC;

If you want all columns to be saved, then specify “*”:

CREATE TABLE `db2`.`new_table` SELECT * FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `id` DESC;

How to save query results to new table with PRIMARY KEY

After the name of the new table in parentheses, specify the name and properties of the column that should become PRIMARY KEY, for example:

CREATE TABLE `db2`.`test` (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) SELECT `column` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column` DESC;

You don't have to name each column that will make up the new table. The data retrieved with the SELECT query will be joined to the table on the right.

How to specify column properties when saving query results to a new table

After the name of the new table in parentheses, you can specify column properties using the CREATE TABLE syntax:

CREATE TABLE `db2`.`test` (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), KEY(title)) SELECT `title`, `link` FROM `db1`.`old_table` WHERE `body` LIKE '%search string%' ORDER BY `id` DESC;

Please note that if the column names obtained using SELECT do not match the names specified in CREATE TABLE, then such columns will be appended to the right.

How to change column names in query results saved to a new table

With the AS keyword, you can change the name of one or more columns.

An example of changing the name of one column:

CREATE TABLE `db2`.`test` SELECT `link` AS `megalink` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column` DESC;

An example of changing the name of two columns to insert into the table being created:

CREATE TABLE `db2`.`test` SELECT `id` AS `name`, `link` AS `megalink` FROM `db1`.`old_table` WHERE `row` LIKE '%search string%' ORDER BY `column` DESC;

How to save query results to an existing table in MySQL

To save search results to an existing table, use the construct:

INSERT INTO new_table SELECT //search terms go here

Note that if you do not specify the name of the columns into which the data is to be inserted, then the names of the columns selected to be saved must match in both tables.

An example of specifying a column name in a new table for inserting data:

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Or use “AS” to change the name of the fields, as shown above.