SQL SELECT INTO
SELECT INTO creates a new table and copies rows into it in one statement. It's a quick way to snapshot or stage data.
Snapshot a table
SQL
SELECT * INTO customers_backup FROM customers;
The new table customers_backup is created with the same columns and types, and populated with all rows from the source.
Subset of columns and rows
SQL
SELECT id, name, email INTO customers_au FROM customers WHERE country = 'AU';
Vendor support
| DB | Form |
|---|---|
| SQL Server / MS Access | SELECT … INTO new_table FROM … |
| PostgreSQL | SELECT … INTO new_table FROM … or CREATE TABLE new AS SELECT … |
| MySQL / MariaDB | No SELECT INTO for tables — use CREATE TABLE new AS SELECT … |
| SQLite / Oracle | CREATE TABLE new AS SELECT … |
What does NOT get copied
Constraints, indexes, default values, and triggers are not carried over by SELECT INTO. If you need them in the snapshot, recreate them after the copy.
Tip: Use
SELECT INTO for one-off snapshots and ad-hoc reporting. For production backups, use the database's native backup tools.Example
Exercise
Copy customers into a brand-new snapshot table.
SELECT *
customers_backup FROM customers;
Four letters; introduces the new table.
Discussion
Loading…