Skip to content
Related Articles

Related Articles

Select into and temporary tables in MS SQL Server
  • Last Updated : 23 Sep, 2020

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;
NameRollnoCourse
Maya111CSE
Naina112ECE
Bobby113EEE
Clara114Mech
Select *
from marks;
NameRollnoMks
Maya11185
Naina11275
Bobby11365
Clara11455
Select mks into student 
from marks;
NameRollnoCourseMks
Maya111CSE85
Naina112ECE75
Bobby113EEE65
Clara114Mech55

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;
NameRollnoCourse
Maya111CSE
Naina112ECE
Bobby113EEE
Clara114Mech
Select name, rollno into temp_table #details 
from student;
NameRollno
Maya111
Naina112
Bobby113
Clara114

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;

NameRollno
Maya111
Naina112
Bobby113
Clara114
My Personal Notes arrow_drop_up
Recommended Articles
Page :