Insert Into Select statement in MS SQL Server
Prerequisites – Insert statement in MS SQL Server, Select statement in MS SQL Server
Consider a university database. There are two tables namely Student and Marks.
In case, the marks of a few students has to be transferred from the marks table to the marks table, there are many ways to do so.
One can use subqueries (query nested in another query) but this is a complex and time consuming process.
Insert Into Select statement makes the job more easier. This statement is used to insert from the other tables.
Syntax :
insert [TOP(exp)[PERCENT]]
into target_table
column_list
query
Example –
Table – Student
Roll number |
Name |
Course |
111 |
Riya |
CSE |
112 |
Apoorva |
ECE |
113 |
Mina |
Mech |
114 |
Rita |
Biotechnology |
115 |
Veena |
Chemical |
116 |
Deepa |
EEE |
Table – Marks
Roll number |
Name |
GPA |
111 |
Riya |
9.5 |
112 |
Apoorva |
9.4 |
113 |
Mina |
8.7 |
114 |
Rita |
8.1 |
115 |
Veena |
7.7 |
116 |
Deepa |
7.1 |
If a user wants to transfer the marks to the Student table, the query is as follows –
insert into marks (roll number, name, gpa)
select roll number, name, course
from student
The values are inserted. To verify, the query is as follows –
select *
from student
Output –
Roll number |
Name |
Course |
Gpa |
111 |
Riya |
CSE |
9.5 |
112 |
Apoorva |
ECE |
9.4 |
113 |
Mina |
Mech |
8.7 |
114 |
Rita |
Biotechnology |
8.1 |
115 |
Veena |
Chemical |
7.7 |
116 |
Deepa |
EEE |
7.1 |
This is another way of inserting the values. By using TOP keyword, we can extract the desired values from the table –
insert TOP (6) PERCENT
insert into marks(roll number, name, gpa)
select roll number, name, course
from student
order by roll number
Output –
Roll number |
Name |
Course |
Gpa |
111 |
Riya |
CSE |
9.5 |
112 |
Apoorva |
ECE |
9.4 |
113 |
Mina |
Mech |
8.7 |
114 |
Rita |
Biotechnology |
8.1 |
115 |
Veena |
Chemical |
7.7 |
116 |
Deepa |
EEE |
7.1 |
There is a slight difference in the query, yet we get the same result set. TOP is optional and it can be used when a user wants to extract only a particular number of rows from table.
Last Updated :
26 Jul, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...