SQL Temporary Tables
Temporary Table:
The Temporary tables are generally created in TempDB. If the last connection is terminated then the tables are deleted automatically. These are generally used to store and process the intermediate data and intermediate results, these tables are used to store the data temporarily.
There are two types of Temporary tables They are:
- Local Temporary Table
- Global Temporary Table
Local Temporary Table:
A local temporary table is available only for the created session. A temporary table is deleted automatically when the last connection is terminated. The temporary table is created by using a single “#” character.
The user can delete the temporary method using the “Drop” query. If the Temporary table is created in stored functions, the temporary tables are deleted automatically after completion of stored functions execution.
The syntax for Creating a Local Temporary Table:
CREATE TABLE #Tablename (column_name1 datatype1, column_name2 datatype2, column_name3 datatype3, ………….., column_namen datatypen);
The syntax for Inserting values into Local Temporary Tables:
INSERT INTO #Tablename values (column_value1, column_value2, column_value3, ………….., column_valuen), (column_value1, column_value2, column_value3, ………….., column_valuen), (column_value1, column_value2, column_value3, ………….., column_valuen), …….., (column_value1, column_value2, column_value3, ………….., column_valuen);
The syntax for Displaying all the values from Local Temporary Tables:
SELECT * from #Tablename;
Displaying column-wise values:
SELECT column_name from #Tablename;
EXAMPLES:
1) Create a Student table and insert the details of the table and display the values of Student Details.
Creating Student Table:
CREATE TABLE #Student (sid int, sname varchar(20), sage int, sgender varchar(20), Phonenumber int);
>Temporary Table Created
Inserting Student details into the Table:
INSERT INTO #Student (1, ‘Abhinav’, 22, ‘Male’, 9895678909);
>Row created
INSERT INTO #Student (2, ‘Ramya’, 24, ‘Female’, 6687654634);
>Row created
INSERT INTO #Student (3, ‘Preetham’, 21, ‘Male’, 9867546453);
>Row created
INSERT INTO #Student (4, ‘Nethranand’, 21, ‘Male’, 7675643423);
>Row created
INSERT INTO #Student (5, ‘Naveen’, 23, ‘Male’, 6567784532);
>Row created
INSERT INTO #Student (6, ‘Harshitha’, 22, ‘Female’, 9867546231);
>Row created
INSERT INTO #Student (7, ‘Bindu’, 26, ‘Female’, 6563412768);
>Row Created
INSERT INTO #Student (8, ‘Nandhini’, 23, ‘Female’, 6785674839);
>Row Created
INSERT INTO #Student (9, ‘Hashish’, 22, ‘Male’, 9453215052);
>Row Created
INSERT INTO #Student (10, ‘Rahul’, 21, ‘Male’, 9998989898);
>Row Created
Displaying the Table with Student Details:
SELECT * from #Student;
Student
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
Displaying one column of the table i.e, the name of the students:
SELECT sname from #Student;
Student
| sname |
| Abhinav |
| Ramya |
| Preetham |
| Nethranand |
| Naveen |
| Harshitha |
| Bindu |
| Nandhini |
| Hashish |
| Rahul |
Displaying one or more columns of the table i.e, the name of the students:
SELECT sid, sname from #Student;
Student
| sid | sname |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
| 4 | Nethranand |
| 5 | Naveen |
| 6 | Harshita |
| 7 | Bindu |
| 8 | Nandhini |
| 9 | Hashish |
| 10 | Rahul |
Global Temporary Table:
Global Temporary table is created by the “##” character before the table name. These are visible to all the connections of the table. These tables are deleted or dropped, when it is referencing the last connection of table or when the table is closed.
The syntax for Creating a Global Temporary Table:
CREATE TABLE ##Tablename (column_name1 datatype1, column_name2 datatype2, column_name3 datatype3, ………….., column_namen datatypen);
The syntax for Inserting values into Global Temporary Tables:
INSERT INTO ##Tablename values (column_value1, column_value2, column_value3, ………….., column_valuen), (column_value1, column_value2, column_value3, ………….., column_valuen), (column_value1, column_value2, column_value3, ………….., column_valuen), …….., (column_value1, column_value2, column_value3, ………….., column_valuen);
The syntax for Displaying all the values from Global Temporary Tables:
SELECT * from ##Tablename;
Displaying column-wise values:
SELECT column_name from ##Tablename;
EXAMPLES:
2) Create an Employee table and insert the details of the table and display the values of Employee Details.
Creating Employee Table:
CREATE TABLE #Employee (eid int, ename varchar(20), eage int, egender varchar(20), Phonenumber int);
>Temporary Table Created
Inserting Employee details into the Table:
INSERT INTO ##Employee (1, ‘Abhinav’, 22, ‘Male’, 9895678909);
>Row created
INSERT INTO ##Employee (2, ‘Ramya’, 24, ‘Female’, 6687654634);
>Row created
INSERT INTO ##Employee (3, ‘Preetham’, 21, ‘Male’, 9867546453);
>Row created
INSERT INTO ##Employee (4, ‘Nethranand’, 21, ‘Male’, 7675643423);
>Row created
INSERT INTO ##Employee (5, ‘Naveen’, 23, ‘Male’, 6567784532);
>Row created
INSERT INTO ##Employee (6, ‘Harshitha’, 22, ‘Female’, 9867546231);
>Row created
INSERT INTO ##Employee (7, ‘Bindu’, 26, ‘Female’, 6563412768);
>Row Created
INSERT INTO ##Employee (8, ‘Nandhini’, 23, ‘Female’, 6785674839);
>Row Created
INSERT INTO ##Employee (9, ‘Hashish’, 22, ‘Male’, 9453215052);
>Row Created
INSERT INTO #Employee (10, ‘Rahul’, 21, ‘Male’, 9998989898);
>Row Created
Displaying the Table with Student Details:
SELECT * from #Student;
Employee
| eid | ename | eage | egender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
Displaying one column of the table i.e, the name of the Employees:
SELECT ename from #Student;
Employee
| ename |
| Abhinav |
| Ramya |
| Preetham |
| Nethranand |
| Naveen |
| Harshitha |
| Bindu |
| Nandhini |
| Hashish |
| Rahul |
Displaying one or More columns of the table i.e, the name of Employee:
SELECT eid, ename from #Employee;
Student
| eid | ename |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
| 4 | Nethranand |
| 5 | Naveen |
| 6 | Harshita |
| 7 | Bindu |
| 8 | Nandhini |
| 9 | Hashish |
| 10 | Rahul |