Understanding SQL INSERT INTO SELECT Statement

Filed Under: SQL
SQL INSERT INTO SELECT 1

In this article, we will be focusing on Working of SQL INSERT INTO SELECT statement altogether.


What is SQL SELECT statement?

SQL SELECT query displays the specified data from the table of a database.

Syntax:

Select * from Table;
       OR
Select column-list from Table;

We can either display all the data or a portion of it engrossed by conditions.

Example:

Select * from Info;

What is SQL INSERT statement?

SQL INSERT query is useful in inserting records into the database according to the specified columns and conditions.

Syntax:

Insert into Table(column-list)values(val1,,,,valN);

Working of SQL INSERT INTO SELECT statement

SQL INSERT INTO SELECT statement enables us to select and copy data from one table to another.

Syntax:

INSERT INTO Table2 (Column-list)
SELECT(Column-list) From Table1;

This statement executes in the following manner:

  • Initially, it selects data from the specified columns of the table1.
  • Further, the selected data from the columns of table1 is inserted into the specified columns of table2

Thus, by using INSERT INTO SELECT, we can copy some particular data from a table and insert it into another table.

The existing data values of table1 and table2 does not get affected by the INSERT INTO SELECT query.

Having understood the working of the INSERT INTO SELECT query, let us now understand the syntax of the same in the below section.


Implementing INSERT INTO SELECT through examples

Initially, we create a Table 1 — ‘Info’ using SQL Create query with the following columns:

  • item_id
  • Price
  • Quantity
  • City
create table Info(item_id integer, Price integer, Quantity integer, City varchar(200);

Then, we insert records into the table 1 — ‘Info’ using SQL Insert query.

insert into Info(item_id, Price,Quantity,City) values(1, 150,30,'Pune');
insert into Info(item_id, Price,Quantity,City) values(2, 100,23,'USA');
insert into Info(item_id, Price,Quantity,City) values(3, 250,5,'UAE');
insert into Info(item_id, Price,Quantity,City) values(3, 800,2,'DENMARK');
Select * FROM Info;

Output: Table 1 — ‘Info’

SQL INSERT INTO SELECT Table 1
INSERT INTO SELECT Table 1

Further, we create Table 2 – ‘Wholesale’ with the following data columns:

  • Object_id
  • Price
  • Quantity
  • City
create table Wholesale(Object_id integer, Price integer, Quantity integer, City varchar(200);

The data values into ‘Wholesale’ are inserted using INSERT query of SQL.

insert into Wholesale(Object_id, Price,Quantity,City) values(1, 2,1500,'DENMARK');
insert into Wholesale(Object_id, Price,Quantity,City) values(2, 4,2000,'USA');
insert into Wholesale(Object_id, Price,Quantity,City) values(3, 250,157,'USA');
insert into Wholesale(Object_id, Price,Quantity,City) values(4, 60,645,'DENMARK');
insert into Wholesale(Object_id, Price,Quantity,City) values(5, 25,45,'Pune');

SELECT * FROM Wholesale; 

Output: Table 2 — ‘Wholesale’

SQL INSERT INTO SELECT Table 2
INSERT INTO SELECT Table 2

Now, In this example, we execute the SQL INSERT INTO SELECT query to perform the following–

  1. select the data values of the column ‘Price’ and ‘City’ from ‘Info’
  2. insert and copy the above-selected records under the column ‘Price’ and ‘City’ of the ‘Wholesale’ table.
INSERT INTO WholeSale (Price, City)
SELECT Price, City FROM Info;

Output:

SQL INSERT INTO SELECT example 1
INSERT INTO SELECT example 1

In this example, we have selected and copied all the data values from the table ‘Info’ and inserted it into the table ‘Wholesale’.

INSERT INTO Wholesale
SELECT * FROM Info;

Note: The data values are copied from table 1 to table 2. But the original data values of the table(from which the data is being copied) remain unaltered.

Output:

SQL INSERT INTO SELECT Example 2
SQL INSERT INTO SELECT Example 2

Conclusion

By this, we have come to the end of this topic. The INSERT INTO SELECT query can be further used alongside different clauses such as WHERE clause, GROUP BY clause, etc. I recommend you to try these out.

For more posts related to SQL, please do visit SQL JournalDev.

And feel free to comment below in case, you come across any doubt!


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages