Open In App

Select into and temporary tables in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 23 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads