SQL – SELECT INTO / CREATE AS
Copy data from one table into a new table using SELECT INTO or CREATE TABLE AS SELECT.
Table of Contents
SELECT INTO (SQL Server syntax) and CREATE TABLE AS SELECT (SQLite/MySQL/PostgreSQL syntax) let you create a new table and populate it with data from an existing table in a single operation. This is perfect for creating backups, snapshots, or derived reporting tables without running separate CREATE TABLE and INSERT statements.
Syntax differences by database
-- SQL Server
SELECT * INTO students_backup FROM students;
-- SQLite / PostgreSQL / MySQL
CREATE TABLE students_backup AS
SELECT * FROM students;
Try It Yourself — Interactive SQL Editor
Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.
Key Points
- Creates a new table and populates it in one statement
- SQLite/PostgreSQL use CREATE TABLE AS; SQL Server uses SELECT INTO
- The new table copies column names and types from the source
- Use for snapshots, backups, and staging tables in ETL pipelines
Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.
In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.