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

Save