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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.