Python SQL SELECT INTO & Table Backups (CREATE TABLE AS SELECT)
`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.
"`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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Clone a Table with CTAS
- Create a table `active_users` from `users` where `status = 'active'`.
- Select all rows from `active_users`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.