SQL Select Into

SQL SELECT INTO

The SELECT INTO statement copies data from one table into a new table. The exact syntax depends on the database system.

SQL Server Syntax

SELECT *
INTO new_table
FROM existing_table;

This creates a new table and copies all rows from the existing table.

MySQL / MariaDB Equivalent

MySQL and MariaDB do not support SELECT INTO in this way. Instead, use CREATE TABLE AS SELECT:

CREATE TABLE new_table AS
SELECT *
FROM existing_table;

Copy Selected Columns

CREATE TABLE active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

Copy Data from Multiple Tables

You can also create a new table based on a JOIN query:

CREATE TABLE customer_orders AS
SELECT c.name, o.order_date
FROM customers AS c
JOIN orders AS o
  ON c.id = o.customer_id;

Structure vs Data

• SELECT INTO / CREATE TABLE AS SELECT copies both structure and data
• Constraints, indexes, and foreign keys may not be copied automatically

Temporary Tables

Some database systems allow temporary tables:

CREATE TEMPORARY TABLE temp_customers AS
SELECT *
FROM customers;

Common Mistakes

• Expecting indexes and constraints to be copied automatically
• Trying to create a table that already exists
• Using SELECT INTO syntax in MySQL (not supported)

Next Step

Continue with SQL INSERT INTO SELECT to learn how to copy data into an existing table.