Create a Table From a Query Result

Materialize a query's output as a new table in one step using `CREATE TABLE AS SELECT` or `SELECT INTO`.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Build a summary table of total spend per customer for completed orders

Dave is excluded because his order has status = 'pending'. Alice's two completed orders (100 + 200) are summed to 300. The newly created customer_totals table holds exactly the aggregated rows — no indexes or constraints are carried over from the source, so additional CREATE INDEX statements are needed if the summary will be queried frequently.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  customer VARCHAR(50),
  amount INT,
  status VARCHAR(20)
);

INSERT INTO
  orders
VALUES
  ('Alice', 100, 'completed'),
  ('Alice', 200, 'completed'),
  ('Bob', 150, 'completed'),
  ('Carol', 400, 'completed'),
  ('Dave', 300, 'pending');

Validated query

SQL

CREATE TABLE customer_totals AS
SELECT
  customer,
  SUM(amount) AS total_spent
FROM
  orders
WHERE
  status = 'completed'
GROUP BY
  customer;

SELECT
  customer,
  total_spent
FROM
  customer_totals
ORDER BY
  total_spent DESC;

Expected result

customer	total_spent
Carol	400
Alice	300
Bob	150

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (
  customer VARCHAR(50),
  amount INT,
  status VARCHAR(20)
);

INSERT INTO
  orders
VALUES
  ('Alice', 100, 'completed'),
  ('Alice', 200, 'completed'),
  ('Bob', 150, 'completed'),
  ('Carol', 400, 'completed'),
  ('Dave', 300, 'pending');

Validated query

SQL

SELECT
  customer,
  SUM(amount) AS total_spent INTO customer_totals
FROM
  orders
WHERE
  status = 'completed'
GROUP BY
  customer;

SELECT
  customer,
  total_spent
FROM
  customer_totals
ORDER BY
  total_spent DESC;

Expected result

customer	total_spent
Carol	400
Alice	300
Bob	150

MySQL, MariaDB, PostgreSQL, and SQLite use `CREATE TABLE ... AS SELECT ...`. SQL Server uses `SELECT ... INTO new_table FROM ...` — the `INTO` clause names the destination table. The resulting rows are identical across engines.

Useful when

Where this command helps.

persisting a pre-aggregated summary so dashboards can query it without re-running a heavy computation
creating a snapshot of filtered rows for offline analysis or a data pipeline stage

Explanation

What the command is doing.

Most engines let you define and populate a table from a query in a single statement. MySQL, MariaDB, PostgreSQL, and SQLite use CREATE TABLE new_table AS SELECT ..., which infers column names and types from the query output. SQL Server uses SELECT ... INTO new_table FROM ..., where the INTO clause names the destination table. Both forms create the table and insert all rows atomically — useful for snapshots, pre-computed summaries, and staging tables that do not need to survive schema changes.

Create a Table From a Query Result

Build a summary table of total spend per customer for completed orders

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.