Select into and temporary tables in MS SQL Server

1. Select into :
Suppose a table has some particular rows that has to be transferred to another table of the same database. It can be done using select into statement as follows –

select list into destination from source (where condition) 

Example :
There are two tables named student and marks. The marks of the students has to be transferred from marks to student table. This has to be done as :

Select *
from student;
Name Rollno Course
Maya 111 CSE
Naina 112 ECE
Bobby 113 EEE
Clara 114 Mech
Select *
from marks;
Name Rollno Mks
Maya 111 85
Naina 112 75
Bobby 113 65
Clara 114 55
Select mks into student 
from marks;
Name Rollno Course Mks
Maya 111 CSE 85
Naina 112 ECE 75
Bobby 113 EEE 65
Clara 114 Mech 55

The marks will be added into the student table. The ‘where’ clause can be used for condition. It is optional.

2. Temporary tables :
The user at times wants to create a separate table from the given table values. It has to be done using the temporary tables concept. Temporary tables can be created in two ways: using create table syntax or select into syntax.

Select into :
A new table has to created from the student table using select into statement as follows :



Select *
from student;
Name Rollno Course
Maya 111 CSE
Naina 112 ECE
Bobby 113 EEE
Clara 114 Mech
Select name, rollno into temp_table #details 
from student;
Name Rollno
Maya 111
Naina 112
Bobby 113
Clara 114

Create table :
A new table can be created using create table statement :

Create table #details( name varchar2(30), rollno int);

A new table is created. The values can be copied from the other table as follows :

Insert into #details select name, rollno from student;

Name Rollno
Maya 111
Naina 112
Bobby 113
Clara 114
My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.