SQL SERVER -- Temp Tables

SQL SERVER -- Temp Tables

                                                        Temp tables are used to store the data temporarily. We can perform DML operations on temp tables as well. 

There are two types of Temp tables available.

1. LOCAL TEMP TABLES:-

                               If we place single # prior to the table name while defining then it is known as local temp table.

SCOPE:- We can use local temp tables only with in a created session

Example:-

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO EMP

VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)

VALUES(4,'Arun',44000,'Hyderabad','m',5,103)

INSERT INTO EMP(EMPID, EMPNAME, DOJ, LOC, GENDER, MagrID, DEPTNO)

VALUES(5,'Aishwarya','01-12-2014','Bangalore','f',3,103)

CREATE TABLE DEPT

(

 DEPTID INT,

 DEPTNAME VARCHAR(100)

)

INSERT INTO DEPT(DEPTID, DEPTNAME) VALUES (101, 'HR')

INSERT INTO DEPT(DEPTID, DEPTNAME) VALUES (102, 'TESTING')

INSERT INTO DEPT(DEPTID, DEPTNAME) VALUES(103, 'DEVELOPMENT')

INSERT INTO DEPT(DEPTID, DEPTNAME) VALUES(104, 'ADMIN')

INSERT INTO DEPT(DEPTID, DEPTNAME) VALUES(105, 'FINANCE')

To retrieve employee info like id, name, location, dept name from both tables:

SELECT A.EMPID, A.EMPNAME,B.DEPTNAME, A.LOC INTO #LOCAL_TEMP_TABLE

FROM EMP AS A INNER JOIN DEPT AS B

ON A.DEPTNO = B.DEPTID

SELECT * FROM #LOCAL_TEMP_TABLE

                       

DML OPERATIONS:-

                   We can perform any DML operations on local temp tables.

2. GLOBAL TEMP TABLES

 

                               If we place double # prior to the table name while defining then it is known as Global temp table.

SCOPE:- We can use Global temp tables in a multiple sessions of a single login page.

EXAMPLE:-

SELECT A.EMPID, A.EMPNAME,B.DEPTNAME, A.LOC INTO ##GLOBAL_TEMP_TABLE

FROM EMP AS A INNER JOIN DEPT AS B

ON A.DEPTNO = B.DEPTID

DML OPERATIONS:-

                 We can perform any DML operation on Global table as well.