SQL - Select Into

SQL: Creating Tables from Queries with SELECT INTO and CTAS

In database management, a common task is to create a new table based on the result of a query. This is useful for backups, archiving, or materializing complex data for reporting. The two primary SQL constructs for this are SELECT ... INTO and CREATE TABLE AS SELECT (CTAS). Their availability and syntax vary across different database systems.

Understanding the Core Concept

Both SELECT INTO and CTAS create a new table and populate it with data from a SELECT statement in a single operation. The new table’s structure (column names and data types) is inferred from the query result.

Crucial Point: These statements only copy the data and the basic column structure. They do not automatically copy constraints (like PRIMARY KEY, FOREIGN KEY, CHECK), indexes, triggers, or permissions from the source table(s). These must be added manually to the new table after its creation.

Method 1: CREATE TABLE AS SELECT (CTAS)

The CTAS statement is part of the ANSI SQL standard and is the most widely supported method, available in systems like PostgreSQL, MySQL, Oracle, and SQLite.

Syntax

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE [condition];

Example: Creating a Full Backup

Let’s start with a modern customers table.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    signup_date DATE NOT NULL,
    city VARCHAR(50)
);

INSERT INTO customers (id, name, email, signup_date, city) VALUES
(1, 'Alice Johnson', 'alice.j@email.com', '2023-01-15', 'New York'),
(2, 'Bob Williams', 'bob.w@email.com', '2023-02-20', 'Los Angeles'),
(3, 'Charlie Brown', 'charlie.b@email.com', '2023-02-22', 'Chicago'),
(4, 'Diana Prince', 'diana.p@email.com', '2023-03-05', 'New York');

The following CTAS statement creates a customers_backup table and copies all data from customers.

-- This works in PostgreSQL, MySQL, Oracle, etc.
CREATE TABLE customers_backup AS
SELECT * FROM customers;

Verification

You can now query the new table, which will have the same data but will be missing the PRIMARY KEY and UNIQUE constraints.

SELECT * FROM customers_backup;

Method 2: SELECT ... INTO

This syntax is specific to some database systems, most notably Microsoft SQL Server (T-SQL). It is not supported by MySQL or PostgreSQL in this form.

Syntax (SQL Server)

SELECT column1, column2, ...
INTO new_table_name
FROM existing_table_name
WHERE [condition];

Example: Copying Specific Columns

Let’s create a new table containing only the names and email addresses of customers from New York, using SQL Server’s syntax.

-- This works in SQL Server
SELECT name, email
INTO new_york_customers
FROM customers
WHERE city = 'New York';

Verification

SELECT * FROM new_york_customers;

name	email
Alice Johnson	alice.j@email.com
Diana Prince	diana.p@email.com

Practical Applications and Best Practices

1.  **Archiving Data:** You can use a `WHERE` clause to copy old records into an archive table before deleting them from the active table.

    ```sql
    -- Archive all records from before 2023
    CREATE TABLE customers_archive_2022 AS
    SELECT * FROM customers WHERE EXTRACT(YEAR FROM signup_date) < 2023;
    ```

2.  **Combining Data:** Use `JOIN`s to create a denormalized table for reporting or analysis.

    ```sql
    CREATE TABLE customer_order_summary AS
    SELECT
        c.id AS customer_id,
        c.name,
        c.email,
        COUNT(o.id) AS number_of_orders,
        SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name, c.email;
    ```

3.  **Performance Considerations:** Creating a table from a large dataset is an I/O and log-intensive operation. It's best performed during off-peak hours. For extremely large tables, consider platform-specific features for minimally logged operations if available.

Common Pitfalls and Debugging

• Forgetting to Add Constraints/Indexes: The most common mistake. After creating the table, always run ALTER TABLE statements to add primary keys, foreign keys, and indexes required for data integrity and performance.
• Syntax Mismatch: Using SELECT INTO on a system like PostgreSQL that expects CREATE TABLE AS. Always check the documentation for your specific database.
• Table Already Exists: Both statements will fail if a table with new_table_name already exists. You must DROP the existing table first if you intend to replace it.