SQL SELECT INTO

SQL Tutorial

Here is a simple and complete guide to the SQL SELECT INTO statement. It includes examples for SQL Server, PostgreSQL, and MySQL.

 What SQL SELECT INTO Does

SELECT INTO creates a new table and inserts the results of a SELECT query into it.

 Commonly used for:

  • Creating backup tables

  • Copying part of a table

  • Creating tables from query results

Supported in: SQL Server, PostgreSQL (variant), Oracle
Not supported in: MySQL (use CREATE TABLE AS SELECT)

 Basic Syntax (SQL Server)

 Copy an entire table

  •  Creates a new table customers_backup
  •  Copies all data from customers

 Example: Copy only filtered rows

  •  New table contains only active customers

 Example: Create a table from a JOIN

  •  Creates a reporting table based on a JOIN result

 SQL Server: Create an empty table with structure only

  •  Table created
  •  Zero rows inserted

 PostgreSQL Equivalent

PostgreSQL does not support SELECT INTO for table creation in modern versions.
Use CREATE TABLE AS:

Equivalent to SQL Server’s SELECT INTO.

 MySQL Equivalent

MySQL also uses CREATE TABLE AS SELECT:

 Performance Notes

SELECT INTO is fast for creating backup/migration tables
 New table inherits data types, but not:

  • Indexes

  • Constraints

  • Primary keys

  • Foreign keys

If you need those, create them separately after the table is created.

 Real-World Use Cases

1. Backup a table before a batch update

2. Create a reporting table

3. Archive old data

You may also like...