CodeCapsule

SQL SELECT INTO

by

Here is a clear, practical, and complete guide to the SQL SELECT INTO statement, including examples for SQL Server, PostgreSQL, and MySQL.

✅ What 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)

SELECT column1, column2, ...
INTO new_table
FROM existing_table
WHERE condition;

📌 Example: Copy an entire table

SELECT *
INTO customers_backup
FROM customers;

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

📌 Example: Copy only filtered rows

SELECT id, name, email
INTO active_customers
FROM customers
WHERE status = 'active';

✔ New table contains only active customers

📌 Example: Create a table from a JOIN

SELECT u.id, u.name, o.total
INTO user_orders
FROM users u
JOIN orders o ON u.id = o.user_id;

✔ Creates a reporting table based on a JOIN result

🔥 SQL Server: Create an empty table with structure only

SELECT *
INTO empty_orders
FROM orders
WHERE 1 = 0;

✔ Table created
✔ Zero rows inserted

🧩 PostgreSQL Equivalent

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

CREATE TABLE new_table AS
SELECT * FROM old_table;

Equivalent to SQL Server’s SELECT INTO.

🧩 MySQL Equivalent

MySQL also uses CREATE TABLE AS SELECT:

CREATE TABLE new_table AS
SELECT * FROM old_table;

🏎 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

SELECT *
INTO employees_backup_2024
FROM employees;

2. Create a reporting table

SELECT department, COUNT(*) AS emp_count
INTO dept_summary
FROM employees
GROUP BY department;

3. Archive old data

SELECT *
INTO orders_archive
FROM orders
WHERE created_at < '2023-01-01';

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *