SQL - Insert Into... Select

In SQL, the INSERT INTO... SELECT statement is used to add/insert one or more new rows (records) from an existing table to another table.

This statement is a combination of two different statements: INSERT INTO and SELECT.

The INSERT INTO statement is one of the most fundamental and frequently used commands in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted matches the structure and data types of the table columns.

The SELECT statement is used to retrieve data from an existing database table.

When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

Syntax

Following is the syntax for using insert into select statement −

INSERT INTO table2
SELECT * FROM table1

Before we are going to use the above query, we have to consider the following points −

In the database where we are going to insert data, a table must already exist.

The table structure of the source and target tables must match.

Example

Let’s consider the following table we are going to create for further explanation of "INSERT INTO SELECT."

CREATE TABLE Agent  
(  
   id INT NOT NULL,  
   name VARCHAR(50) NOT NULL,  
   gender VARCHAR(20) NOT NULL,  
   age INT NOT NULL,
   PRIMARY KEY(ID)
 );  

Now, we are going to populate the table −

INSERT INTO Agent  
VALUES (1, Msd ,  Male , 21),   
(2, Virat ,  Male , 23),   
(3, Perry ,  Female , 24),   
(4, Smiti ,  Female , 18),   
(5, Rose ,  Female , 23),   
(6, Jack ,  Male , 22);

We can use the following query to check whether the table has been created or not −

SELECT * FROM Agent;

On running it will display a table as −

+----+-------+--------+-----+
| id | name  | gender | age |
+----+-------+--------+-----+
|  1 | Msd   | Male   |  21 |
|  2 | Virat | Male   |  23 |
|  3 | Perry | Female |  24 |
|  4 | Smiti | Female |  18 |
|  5 | Rose  | Female |  23 |
|  6 | Jack  | Male   |  22 |
+----+-------+--------+-----+

Now we are going to create a table and use it as a demonstration for our examples −

CREATE TABLE Agentdemo  
(  
   id INT NOT NULL,  
   name VARCHAR(50) NOT NULL,  
   gender VARCHAR(20) NOT NULL,  
   age INT NOT NULL,
	PRIMARY KEY(ID)
 ); 

Inserting required data from one table to another table

Sometimes we only need to add a small number of records to another table. This can be accomppshed by using a WHERE clause to select the number of rows that the query returned.

Execute the following query, which fetches the gender ="male" from table Agent and inserts it in the table Agentdemo.

INSERT INTO Agentdemo(id,name, gender, age)  
SELECT id,name, gender, age FROM Agent  
WHERE gender =  male ;

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 Agentdemo table −

SELECT * FROM Agentdemo;

On running the above query, it will generate the following output, as shown below −

+----+-------+--------+-----+
| id | name  | gender | age |
+----+-------+--------+-----+
|  1 | Msd   | Male   |  21 |
|  2 | Virat | Male   |  23 |
|  6 | Jack  | Male   |  22 |
+----+-------+--------+-----+

Inserting the top N rows required

The TOP clause details the number of rows from the query that should be added to the target table. This may be accomppshed by first truncating all rows in the Agentdemo table using the following statement −

TRUNCATE TABLE Agentdemo; 

Now, we are going to insert the top 3 agents sorted by their age by using the following query, shown below −

INSERT TOP (3) INTO Agentdemo(id,name, gender, age)  
SELECT id,name, gender, age FROM Agent  
ORDER BY age;

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 Agentdemo table −

SELECT * FROM Agentdemo;

It will display the output as shown below after running the above query.

+----+-------+--------+-----+
| id | name  | gender | age |
+----+-------+--------+-----+
|  1 | Msd   | Male   |  21 |
|  2 | Virat | Male   |  23 |
|  3 | Perry | Female |  24 |
+----+-------+--------+-----+

Inserting all data from one table to another table

Imagine that we want to add every piece of information from the Agent table to the Agentdemo table. To do this, first truncate all rows in the Agentdemo table by using the statement −

TRUNCATE TABLE Agentdemo; 

Execute the following query to add all information from the Agent table to the Agentdemo table −

INSERT INTO Agentdemo(id,name, gender, age)  
SELECT id,name, gender, age FROM Agent; 

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 Agentdemo table −

SELECT * FROM Agentdemo;

On executing the above query, it will generate an output as shown below −

+----+-------+--------+-----+
| id | name  | gender | age |
+----+-------+--------+-----+
|  1 | Msd   | Male   |  21 |
|  2 | Virat | Male   |  23 |
|  3 | Perry | Female |  24 |
|  4 | Smiti | Female |  18 |
|  5 | Rose  | Female |  23 |
|  6 | Jack  | Male   |  22 |
+----+-------+--------+-----+