🧱SELECT INTO / CTAS

Create and populate a table from a query

SELECT INTO (SQL Server and Sybase family) executes a query and stores the result in a new table, inferring column names and types from the projection. CREATE TABLE AS (CTAS) is the broader ANSI-shaped pattern: explicit CREATE TABLE … AS SELECT … (PostgreSQL, BigQuery, Snowflake, Redshift, etc.). Both are DDL + load in one statement, permissions, locks, and transaction boundaries differ by engine.

For versioned application schema, teams often prefer CREATE TABLE with explicit column types and constraints, followed by INSERT … SELECT, so migrations are reviewable and type choices are not left to the optimizer’s inference.

Dialect	Common spelling
SQL Server	SELECT * INTO archive.runs_2025 FROM dbo.runs WHERE run_year = 2025;
PostgreSQL	CREATE TABLE archive.runs_2025 AS SELECT * FROM runs WHERE run_year = 2025;
MySQL	CREATE TABLE archive_runs_2025 AS SELECT * FROM runs WHERE run_year = 2025;

⚡ Advanced / dialect note

SQL Server: SELECT * INTO archive.runs_2025 FROM dbo.runs WHERE run_year = 2025;
PostgreSQL: CREATE TABLE archive.runs_2025 AS SELECT * FROM runs WHERE run_year = 2025;: add explicit casts or a separate CREATE TABLE + INSERT when you need non-inferred types and constraints.

SQL

CTAS-style reporting snapshot (portable mental model)

CREATE TABLE reporting.top_genes AS
SELECT gene_symbol, COUNT(*) AS mentions
FROM literature_hits
WHERE year = 2026
GROUP BY gene_symbol;

✓Illustrative result set

status
Table created + rows loaded

Type inference picks FLOAT / DOUBLE for a division

Inferred types from expressions can widen silently, financial columns become floating point, losing exactness and breaking downstream reconciliation.

Cast in the SELECT list or define DDL explicitly

Use ::numeric(18,4) (Postgres) or DECIMAL(18,4) in explicit CREATE TABLE, or wrap the CTAS in a migration that asserts types with CAST.

🏢Warehouses and MPP

Distribution keys and sort keys rarely infer correctly from CTAS alone.

On clustered columnar stores, follow CTAS with ALTER TABLE … DISTKEY / SORTKEY equivalents (vendor specific) or create the table empty with full DDL, then INSERT: so load performance and co-located joins match the model you designed.

Scratch vs prod

Ad hoc SELECT INTO from a GUI connected to production is a classic footgun, role-separate connections, naming conventions like tmp_username_…, and automated retention jobs reduce orphan heap tables.

Locks

Creating a table from a large scan can hold metadata locks and fill transaction logs / WAL, run during windows or use UNLOGGED / minimal-logging patterns only where your ops team has blessed them.

Idempotent pipelines

For ETL reruns, prefer CREATE TABLE IF NOT EXISTS + TRUNCATE + INSERT, or merge patterns with staging keys, blind CTAS each run leaves a trail of _v2 tables nobody dares drop.

TL;DR

SELECT … INTO new_table (SQL Server style) or CREATE TABLE AS SELECT (PostgreSQL) creates a new table from a query result, fast for staging and one-off extracts. It copies data types and sometimes omits constraints/indexes you still need, add keys and NOT NULLs explicitly afterward. Not portable verbatim across engines; treat as ETL scaffolding, not the final production DDL.