SQL SELECT INTO Statement

SQL SELECT INTO Statement

The SELECT INTO statement in SQL is used to copy data from one table into a new table. It creates the new table automatically and inserts the selected data into it, all in a single step.

This statement is useful when you want to create a backup of a table, copy only specific data, or generate temporary tables for analysis.

The SELECT INTO statement is commonly supported by SQL Server, PostgreSQL, and some other databases. In MySQL, you can use the CREATE TABLE ... SELECT ... statement instead, as it does not support the SELECT INTO statement.

Syntax

Following is the basic syntax to copy all columns from one table to a new table in SQL Server:

SELECT * INTO new_table
FROM existing_table;

Example: Copying All Columns

Let us create the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert few records into this table using the INSERT statement as follows:

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Now, you can create a copy of this table using the SELECT INTO statement in SQL server as follows:

SELECT * INTO CUSTOMERS_BACKUP
FROM CUSTOMERS;

Following is the output obtained:

(7 rows affected)
Completion time: 2025-08-05T16:21:57.0492462+05:30

This will create a new table named CUSTOMERS_BACKUP with the same columns and data as the CUSTOMERS table.

Verification

You can now run a SELECT query to view the contents of the new table as shown below:

SELECT * FROM CUSTOMERS_BACKUP;

The table displayed is as follows:

Copying Specific Columns Only

You can also copy only selected columns into the new table if you don't need the entire data. This allows you to include just the relevant information.

Syntax

Following is the syntax to copy specific columns from one table to another in SQL Server:

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

Example

The following statement creates a new table named CUSTOMER_NAMES with only the ID and NAME columns:

SELECT ID, NAME INTO CUSTOMER_NAMES
FROM CUSTOMERS;

We get the output as shown below:

(7 rows affected)
Completion time: 2025-08-05T16:25:55.9850586+05:30

Verification

To confirm that the new table CUSTOMER_NAMES has been created and contains the expected data, you can use the following SELECT statement:

SELECT * FROM CUSTOMER_NAMES; 

This will display all the rows and columns from the newly created CUSTOMER_NAMES table as shown below:

Using WHERE Clause with SELECT INTO

You can use a WHERE clause with SELECT INTO statement to insert only the rows that match a specific condition into the new table.

Syntax

Following is the basic syntax to use WHERE clause with the SELECT INTO statement in SQL Server:

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

Example

The following query creates a new table with customers whose AGE is greater than 25:

SELECT * INTO CUSTOMERS_OVER25
FROM CUSTOMERS
WHERE AGE > 25;

You get the output as shown below:

(2 rows affected)
Completion time: 2025-08-05T16:35:02.2841771+05:30

Verification

To check if the new table CUSTOMERS_OVER25 has been created correctly and includes only the desired records, use the following query:

SELECT * FROM CUSTOMERS_OVER25;

This will display all the rows from the CUSTOMERS_OVER25 table, showing only customers whose age is greater than 25:

Copying from Joins Using SELECT INTO

The SELECT INTO statement can also be used with joins to retrieve data from multiple tables. This allows you to create a new table containing combined results based on related data from those tables.

Syntax

Following is the basic syntax to copy data from joins using the SELECT INTO statement in SQL Server:

SELECT t1.column1, t2.column2, ...
INTO new_table
FROM table1 t1
JOIN table2 t2 ON t1.common_column = t2.common_column
WHERE condition;

Example

Assume you have another table named ORDERS having the following structure:

Now, the following query joins the CUSTOMERS and ORDERS tables using the customer ID, and copies the matching data into a new table named CUSTOMER_ORDERS. The new table will include customer ID, name, order ID, and amount for each order:

SELECT C.ID, C.NAME, O.ORDER_ID, O.AMOUNT
INTO CUSTOMER_ORDERS
FROM CUSTOMERS C
JOIN ORDERS O ON C.ID = O.CUSTOMER_ID;

We get the output as shown below:

(3 rows affected)
Completion time: 2025-08-05T17:02:18.9907316+05:30

Verification

To verify that the data has been copied correctly into the new table, run the following query:

SELECT * FROM CUSTOMER_ORDERS;

The table displayed is as shown below:

INSERT INTO SELECT vs SELECT INTO

Both INSERT INTO SELECT and SELECT INTO statements are used to copy data from one table to another, but they work differently based on whether the target table already exists:

SELECT * INTO NEW_TABLE
FROM EXISTING_TABLE
WHERE condition;

INSERT INTO EXISTING_TABLE (col1, col2)
SELECT col1, col2
FROM SOURCE_TABLE
WHERE condition;

Important Points About SELECT INTO Statement

Following are some of the important points to remember about the SELECT INTO statement in SQL:

• The SELECT INTO statement automatically creates a new table. If the table already exists, it will result in an error.
• It is useful for creating backups, generating reports, or setting up temporary tables for analysis.
• You can combine it with clauses like WHERE, JOIN, ORDER BY, and others to control the data being copied.
• It is not supported in MySQL. Instead, use CREATE TABLE ... SELECT ... as an alternative.