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:
|
1 2 3 |
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:
|
1 |
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:
|
1 |
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:
|
1 2 |
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:
|
1 |
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.
|
1 2 3 4 |
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'); |
|
1 |
Select * FROM Info; |
Output: Table 1 — ‘Info’
Further, we create Table 2 – ‘Wholesale’ with the following data columns:
- Object_id
- Price
- Quantity
- City
|
1 |
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.
|
1 2 3 4 5 |
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'); |
|
1 |
SELECT * FROM Wholesale; |
Output: Table 2 — ‘Wholesale’
Now, In this example, we execute the SQL INSERT INTO SELECT query to perform the following–
- select the data values of the column ‘Price’ and ‘City’ from ‘Info’
- insert and copy the above-selected records under the column ‘Price’ and ‘City’ of the ‘Wholesale’ table.
|
1 2 |
INSERT INTO WholeSale (Price, City) SELECT Price, City FROM Info; |
Output:
In this example, we have selected and copied all the data values from the table ‘Info’ and inserted it into the table ‘Wholesale’.
|
1 2 |
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:
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!