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.

My Personal Notes arrow_drop_up

Save