Back to list Standard SQL How to Create a Table from an SQL Query Database: SQL MySQL PostgreSQL Oracle SQL Server SQLite Operators:CREATE TABLE AS SELECT, SELECT INTO 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. idgamerscorechampionship_date 1alice142020-08-10 2supervisor102020-09-28 3happyman02020-08-10 4lukas62020-08-10 5oli122020-08-10 6biggamer72020-09-12 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: gamerscorechampionship_date alice142020-08-10 happyman02020-08-10 lukas62020-08-10 oli122020-08-10 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. Recommended courses: The Basics of Creating Tables Data Types in SQL SQL Constraints Recommended articles: How to Create a Table in SQL Referential Constraints and Foreign Keys in MySQL Understanding Numerical Data Types in SQL See also: How to Create One Table From Another Table in SQL Tags: SQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.