---
title: "CREATE TABLE AS SELECT (CTAS)"
description: "CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data."
canonical: "https://motherduck.com/glossary/create-table-as-select/"
related:
  - title: "TEMPORARY TABLES | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/temporary-tables/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "CREATE TABLE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-table/"
---

# CREATE TABLE AS SELECT (CTAS)

> CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.

## Overview
CTAS combines table creation and data loading into a single, atomic statement — no need to declare column types up front, since they're inferred from the query's result:

```sql
CREATE TABLE monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS revenue
FROM orders
GROUP BY ALL;
```

This is the standard pattern for materializing intermediate results, snapshotting a table at a point in time, or restructuring data (e.g., pivoting or denormalizing) into a new physical table for faster downstream querying.

<glossary-callout guide="duckdb-cheatsheet-full" />

## CTAS vs a view
A CTAS-created table stores a physical copy of the query's result at creation time — it does not update automatically if the underlying source tables change. A `VIEW`, by contrast, stores only the query definition and re-executes it on every read. Use CTAS when you want to freeze/materialize a result for repeated fast access; use a view when you always want the latest data and can tolerate re-computing it per query.

## DuckDB specifics
DuckDB supports standard `CREATE TABLE ... AS SELECT ...`, plus `CREATE OR REPLACE TABLE ... AS SELECT ...` to atomically replace an existing table (dropping and recreating it) in one statement, which is convenient in ELT scripts that rebuild a table on every run:

```sql
CREATE OR REPLACE TABLE monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS revenue
FROM orders
GROUP BY ALL;
```

DuckDB also supports `CREATE TEMP TABLE ... AS SELECT ...` for a session-scoped materialization, and CTAS works seamlessly with DuckDB's file-reading functions, so you can materialize a table directly from external files: `CREATE TABLE t AS SELECT * FROM read_parquet('s3://bucket/data/*.parquet')`.