Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Insert Into Select statement in MS SQL Server

  • Last Updated : 26 Jul, 2020

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.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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 numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE



Table – Marks
Roll numberNameGPA
111Riya9.5
112Apoorva9.4
113Mina8.7
114Rita8.1
115Veena7.7
116Deepa7.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 numberNameCourseGpa
111RiyaCSE9.5
112ApoorvaECE9.4
113MinaMech8.7
114RitaBiotechnology8.1
115VeenaChemical7.7
116DeepaEEE7.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 numberNameCourseGpa
111RiyaCSE9.5
112ApoorvaECE9.4
113MinaMech8.7
114RitaBiotechnology8.1
115VeenaChemical7.7
116DeepaEEE7.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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!