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 |
Last Updated :
23 Sep, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...