SQL - Select Into

The SQL SELECT INTO command creates a new table and inserts data from an existing table into the newly created table. The new table is created automatically based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database.

However, it s important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary.

SELECT INTO - Coping Data From All Columns

The SQL SELECT INTO command is used to create a new table and populate it with the data selected from an existing table.

Syntax

Following is the basic syntax for copying all the columns into a new table using SELECT INTO command −

SELECT *
INTO new_table_name
FROM existing_table_name

Example

To understand it better 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 insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

If you retrieve the contents of the above created table using the SELECT command you will get the following output −

select * from Customers;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The table will be created as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, we are trying to create a new table called "CUSTOMER_BACKUP" with the same columns as the "CUSTOMERS" table, and populate it with all the data from "CUSTOMERS" table using the following SELECT INTO query −

SELECT *
INTO CUSTOMER_BACKUP
FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table −

SELECT * from CUSTOMER_BACKUP;

The table displayed is as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SELECT INTO - Copying Data From Specific Columns

We can also copy specific columns by including their names in the SELECT statement from an existing table into the new table using the SELECT INTO statement.

Syntax

Following is the basic syntax for copying specific columns into a new table using SELECT INTO command −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM existing_table_name;

Example

In the following query we are trying to create a new table called "CUSTOMER_DETAILS" with only the "name", “age”, and "address" columns from the "CUSTOMERS" table, and populate it with the corresponding data.

SELECT name, age, address
INTO CUSTOMER_DETAILS
FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table −

SELECT * from CUSTOMER_DETAILS;

The table displayed is as follows −

+----------+-----+-----------+
| name     | age | address   | 
+----------+-----+-----------+-
| Ramesh   |  32 | Ahmedabad | 
| Khilan   |  25 | Delhi     | 
| kaushik  |  23 | Kota      | 
| Chaitap |  25 | Mumbai    | 
| Hardik   |  27 | Bhopal    | 
| Komal    |  22 | MP        | 
+----------+-----+-----------+

Note − The new table will not include any other columns from the original table. Also the original table remains unchanged.

SELECT INTO - Copying Data From Multiple Tables

The SQL SELECT INTO command can also be used to copy data from more than one table into a new table. This is accomppshed using a JOIN statement to combine the data from multiple tables and then copying the result set into the new table.

Syntax

Following is the basic syntax for using SELECT INTO to copy data from multiple table −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM table1
JOIN table2 ON table1.column = table2.column

Example

In the following example, let us create another table ORDERS to help in demonstrating the usage of SELECT INTO command to copy data from multiple tables into the new table −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102,  2009-10-08 00:00:00 , 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100,  2009-10-08 00:00:00 , 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101,  2009-11-20 00:00:00 , 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103,  2008-05-20 00:00:00 , 4, 2060.00);

The table is displayed as follows −

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+

Now, we are trying to create a new table called "CUSTOMERS_ORDER" that includes the customer name from the "customers" table and the customer id from the "ORDERS" table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table −

SELECT CUSTOMERS.Name, ORDERS.customer_id
INTO CUSTOMERS_ORDER
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id;

Output

We get the following result. We can observe that 8 rows have been modified.

(8 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMERS_ORDER table −

SELECT * FROM CUSTOMERS_ORDER;

The table displayed is as follows −

+----------+-------------+
| Name     | customer_id |
+----------+-------------+
| Ramesh   |   NULL      |
| Khilan   |   2         |
| kaushik  |   3         |
| kaushik  |   3         |
| Chaitap |   4         |
| Hardik   |   NULL      |
| Komal    |   NULL      |
| Muffy    |   NULL      |
+----------+-------------+

SELECT INTO with WHERE clause

We can also use the SELECT INTO command with a WHERE clause to create a new table and copy specific rows from an existing table into it. The WHERE clause allows us to specify criteria for selecting only the rows that meet certain conditions.

Syntax

Following is the basic syntax for using SELECT INTO with a WHERE clause −

SELECT *
INTO new_table_name
FROM existing_table_name
WHERE condition;

Here, the WHERE clause specifies the condition that must be met for a row to be included in the new table.

Example

In the following query we are trying to create a new table called "NameStartsWith_K" that includes all columns from the "CUSTOMERS" table, but only for the customers whose name starts with ‘K’.

SELECT *
INTO NameStartsWith_K
FROM CUSTOMERS
WHERE NAME LIKE  k% 

Output

We get the following result. We can observe that 3 rows have been modified.

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the NameStartsWith_K table −

SELECT * from NameStartsWith_K;

The table displayed is as follows −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
+----+----------+-----+-----------+---------+