SQL Server select into temp table

Many a times we get an output, and then query the output further. In such scenarios instead of writing the entire query repeatedly, and then improvising it or filtering it differently, we can dump the output of the main query into a temporary table i.e. Select into Temp table. Later we can query the temporary table & improvise the outputs as needed.

Let us create the sample tables.

CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);


CREATE TABLE DEPT
       (DEPTNO INTEGER Primary Key,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );

INSERT INTO DEPT VALUES
        (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
        (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
        (40,'OPERATIONS','BOSTON');

Now we will write a complex looking select statement as shown here –

select ename, emp.deptno, sal, 
           case when sal >= 5000 then 'Grade A'
                when sal >= 3000 then 'Grade B'
                when sal >= 2500 then 'Grade C'
                when sal >= 2000 then 'Grade D'
                when sal >= 1000 then 'Grade E'
                else
                'Grade F'
            end as Sal_Grade,
            datepart(yy, hiredate) as Year_Of_Hiredate,
            DName,
           Sal + IsNull(Comm,0) as Total,
           Case
            when Comm is null or Comm = 0 then 'Commission not provided'
            when Comm is not null then 'Commission provided'
            End as Commission_Status,
               SubString(Loc,1,1) as Location_ID
from emp join dept
on Emp.deptno = Dept.deptno

The output of this query is as as follows –



In this query, we have used the following techniques:

1. Two Case Functions
2. Date Function
3. IsNull Function
4. Substring Function
5. Inner Join

Now improvise this output. Put this output into a local temporary table by issuing the following query:

Select * into #Emp_Info
from(select ename, emp.deptno, sal, 
           case when sal >= 5000 then 'Grade A'
                when sal >= 3000 then 'Grade B'
                when sal >= 2500 then 'Grade C'
                when sal >= 2000 then 'Grade D'
                when sal >= 1000 then 'Grade E'
                else
                'Grade F'
            end as Sal_Grade,
            datepart(yy, hiredate) as Year_Of_Hiredate,
            DName,
           Sal + IsNull(Comm,0) as Total,
           Case
            when Comm is null or Comm = 0 then 'Commision not provided'
            when Comm is not null then 'Commision provided'
            End as Commission_Status,
               SubString(Loc,1,1) as Location_ID
from emp join dept
on Emp.deptno = Dept.deptno
) as Emp_Dept

So now these 14 processed rows are the actual records of #Emp_Info table. Therefore now when we will query this table, the inner join, case, date, substring & IsNull functions do not get executed again & again. This will be a big performance boost!

Let’s explore different outputs querying the #Emp_Info table now.

Query 1 – Display records of Grade B employees

Select * from #Emp_Info
where Sal_Grade = 'Grade B'
go

Query 2 – Display grade wise total of salaries

Select Sal_Grade, Sum(Sal) as Salary_Sum
from #Emp_Info
Group by Sal_Grade
go

Query 3 – Display employee names, totals, DName and year of hiredate for all employees who have joined in 1981.

Select Ename, DName, Total, Year_of_Hiredate
from #Emp_Info
where Year_of_Hiredate = '1981'

So as we can see, doing a T-SQL Select into temp table can help us fire additional queries on the output, and in some cases, give a performance boost too