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 –

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 –

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 –

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.

mangalgiaishwarya2

Check out this Author's contributed articles.