SQL Select Into

Overview

SQL SELECT INTO query is used to select data from one table and to copy that selected data into new table. Either we can copy all the data or we can also select a subset of data from the existing table which satisfies the given condition. Largely it is used to take backup or to copy the existing rows, records from one table to another new table. we can also say that SQL SELECT INTO is used to create new table by copying data from existing table.

Syntax

Syntax for SQL SELECT INTO statement is

SELECT column_name INTO new_table_name FROM existing_table_name;

Examples

Let us look more into it with the help of an example

Consider table hostel_list which contains list of hostels.

+----+-------------------+-----------+----------+
| id | name              | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 |
|  2 | KAILASH BHAVAN    |         2 |      990 |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 |
|  4 | KAVERI BHAVAN     |         1 |      690 |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 |
|  6 | NARMADA BHAVAN    |         6 |     3240 |
|  7 | GANGA BHAVAN      |         1 |      780 |
|  8 | YAMUNA BHAVAN     |         8 |     4580 |
+----+-------------------+-----------+----------+

Let us create a another table with the same data as of hostel_list and say we call that table as hostel_list_archive. SQL query for that will be

SELECT * INTO hostel_list_archive FROM hostel_list;

+----+-------------------+-----------+----------+
| id | name              | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 |
|  2 | KAILASH BHAVAN    |         2 |      990 |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 |
|  4 | KAVERI BHAVAN     |         1 |      690 |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 |
|  6 | NARMADA BHAVAN    |         6 |     3240 |
|  7 | GANGA BHAVAN      |         1 |      780 |
|  8 | YAMUNA BHAVAN     |         8 |     4580 |
+----+-------------------+-----------+----------+

In MYSQL database you can use this feature as by writing SQL query like

CREATE TABLE hostel_list_archive AS SELECT * FROM hostel_list;