Lesson 19 of 61

5 mins read

SQL SELECT INTO & Table Backups (CREATE TABLE AS SELECT)

In Plain English

`SELECT INTO` (and its ANSI/PostgreSQL/SQLite equivalent `CREATE TABLE ... AS SELECT`) copies data from an existing table into a BRAND NEW destination table created on the fly. It is commonly used for creating backup snapshots, test datasets, and archiving historical records.

The Mental Model

"`SELECT INTO` is like duplicating a document to a fresh backup file: reading the original and saving an exact snapshot copy under a new name in one step."

Deep Dive: How It Works

SQL Server / MS Access Syntax: `SELECT * INTO backup_customers FROM customers WHERE country = 'USA';`.

ANSI / Postgres / SQLite / MySQL Syntax: `CREATE TABLE backup_customers AS SELECT * FROM customers WHERE country = 'USA';`.

Constraint Limitation: `SELECT INTO` / `CTAS` copies column names and data types, but does NOT copy indexes, foreign keys, or default constraints from the original table.

Syntax Blueprint

-- ANSI / PostgreSQL / SQLite / MySQL CTAS pattern:
CREATE TABLE table_backup AS
SELECT * FROM original_table
WHERE active = 1;

Use CREATE TABLE AS SELECT to duplicate data into a new table in one statement.

Core Rules to Remember

New Table Creation: Automatically creates the destination table based on query column types.

Backup Snapshots: Ideal for taking quick snapshot backups before executing complex data migrations.

Missing Constraints: Remember that primary keys and indexes must be manually re-created on the new table.

Common Beginner Traps & How to Fix Them

Mistake:Assuming `CREATE TABLE AS SELECT` copies over primary key auto-increment triggers and foreign keys.

Why it happens: CTAS only creates raw column definitions matching the query output types.

How to fix: Explicitly add primary keys and indexes to the backup table if required.

Pro Tip: Create an empty copy of a table schema with zero rows using `CREATE TABLE empty_copy AS SELECT * FROM source_table WHERE 1 = 0;`.

Live Interactive Example

Hit Run Code to see it live

Creating a Backup Table with CREATE TABLE AS SELECT

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: CREATE TABLE AS SELECT created the vip_archive table and populated it with filtered VIP records.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Clone a Table with CTAS

Create a table `active_users` from `users` where `status = 'active'`.
Select all rows from `active_users`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.