Open In App
Related Articles

Insert Into Select statement in MS SQL Server

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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.

Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation course.


Last Updated : 26 Jul, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads