Open In App

Insert Into Select statement in MS SQL Server

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


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