How to Create a Table as Select in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
CTAS builds a new table from a query's result set. It keeps NOT NULL constraints but not primary keys, foreign keys, or indexes, so add those back after.
CREATE TABLE orders_archive
AS
SELECT *
FROM orders
WHERE order_date < DATE '2020-01-01';Empty copy of a table's structure
A WHERE clause that can never match copies the column definitions with zero rows, which is a common way to stand up a staging or template table.
CREATE TABLE orders_staging
AS
SELECT *
FROM orders
WHERE 1 = 0;NOLOGGING and parallel for large copies
For big one-off loads, NOLOGGING skips most redo generation and PARALLEL splits the copy across multiple server processes, which speeds up the operation considerably. Both trade off recoverability: an unrecoverable failure right after a NOLOGGING load can leave the table unrecoverable from redo until the next backup.
CREATE TABLE orders_archive
NOLOGGING
PARALLEL 4
AS
SELECT *
FROM orders
WHERE order_date < DATE '2020-01-01';The real gotcha with CTAS: it copies column-level NOT NULL constraints from the source, but it never copies the primary key, unique constraints, foreign keys, indexes, or triggers. A table built this way looks identical to the source but has no keys and no indexes until you add them explicitly.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free