Temp Tables

Remember, you don't have to create structure of temp table....


select deptName, deptId, count(*) as TotalEmp
into #TempEmpCount
from tblEmp join tblDept on tblEmp.DeptId = tblDept.DeptId
group by DeptName, DeptId


select DeptName, TotEmp
from #TempEmpCount
where TotalEmp >= 2

Drop Table #TempEmpCount

=====
Dropping is a "good practice"

Temp tables are stored in TempDB.
SCOPE: local only for current session, can be shared b/w nested sp calls.  Global are visible to other sessions and are destroyed when last connection referencing the table is closed.


=====

If you want to have a specific structure, use TABLE VARIABLE:

Declare @tblEmpCount table(DeptName nvarchar(20), DeptId int, TotalEmp int)

Insert @tblEmpCount
select DeptName, DeptId, Count(*) as TotalEmp from tblEmp......

Select DeptName, TotalEmp
from @tblEmpCount

====
NOTE: You don't have to drop table variables. And you can pass table variables as parameters between procedures.
===========

DERIVED TABLES:

select DeptName, TotEmp
from (
Select DeptName, DeptId, COUNT(*) as TotEmp
from tblEmp
join tblDept......
)  as EmpCount
where TotalEmp >= 2


==========
New in SQL SERVER 2005: CTE (Common Table Expressions)
Similar to derived table....

With EmpCount(DeptName, DeptId, TotEmp)
as (
Select DeptName, DeptId, COUNT(*) as TotEmp
from tblEmp
join tblDept...
)
Select DeptName, TotEmp
from EmpCount
where TotEmp >=2