How to Create a Table from an SQL Query

Problem:

You would like to create a new table in a database with data defined by an SQL query.

Example:

We would like to create the table gamer based on an SQL query. In this query, we select data from another table named championship presented below.

In the database, let’s create a new table named gamer which will store data in all of the columns defined in the table championship (id, gamer, score, and championship_date).

Solution 1:

CREATE TABLE gamer 
AS  
SELECT
  *
FROM championship;

Discussion:

If you would like to create a new table, the first step is to use the CREATE TABLE clause and the name of the new table (in our example: gamer). Then, use the AS keyword and provide a SELECT statement that selects data for the new table. In our example, we selected all columns from the table championship by using the asterisk (*). The result set displays all of the records in the table championship.

However, if you want to create a table with only a subset of the records, you can specify the selected query like in the example below.

Solution 2:

CREATE TABLE gamer 
AS  
SELECT
  gamer, score, championship_date
FROM championship 
WHERE championship_date <= 2020-08-10;

Here is the result set:

The SELECT query retrieves only the records with a championship_date date equal to or older than 2020-08-10 (WHERE championship_date <= 2020-08-10). The new table stores fewer columns than in the previous example (SELECT gamer, score, championship_date) without the column id.

A similar solution to this problem is to use the SELECT INTO clause to create a new table and copy data from another table. Take a look at the code:

Solution 3:

SELECT
  gamer, score, championship_date
INTO gamer
FROM championship 
WHERE championship_date <= 2020-08-10;

The resulting table is the same. Note that this construction is not present in the SQL standard. In this SQL command, we first write SELECT, then the list of columns, next the keyword INTO, and finally the name of the new table we want to create. Next, you can add WHERE and other SQL clauses like GROUP BY or HAVING to filter records for the new table.