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