Using a Query to Create a Table

You can use the result of a query to create a new table by putting a SELECT statement inside the CREATE TABLE command.

The following example creates a table that contains a subset of the customer_contacts table for one customer. This is also created as a temporary table, so it will be destroyed when you end your MySQL sessions.

 mysql> CREATE TEMPORARY TABLE scicorp_customers AS     -> SELECT * FROM customer_contacts     -> WHERE customer_code ='SCICORP'; Query OK, 4 rows affected (0.01 sec) Records: 4  Duplicates: 0  Warnings: 0 

The keyword AS is optional but can be included to make the command more readable. If you view the structure of the new table, you will see that all the columns from customer_contacts are present.

 mysql> DESC scicorp_customers; +---------------+-------------+------+-----+---------+-------+ | Field         | Type        | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | contact_id    | int(11)     | NO   |     | 0       |       | | customer_code | varchar(10) | NO   |     |         |       | | first_name    | varchar(30) | NO   |     |         |       | | last_name     | varchar(30) | NO   |     |         |       | | email         | text        | YES  |     | NULL    |       | | telephone     | text        | YES  |     | NULL    |       | +---------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 

Notice, however, that the contact_id column is no longer a PRIMARY KEY for the table and is not an AUTO_INCREMENT column. Keys are not copied when you create a table using this method, but MySQL does retain the data types of the columns and any NOT NULL constraints.