CTE or temporary tables

hi exsperts,

WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
      FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
      FROM EmpOrg_CTE AS parent
      JOIN HR.Employees AS child
      ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;

can you gou givme this code with temporary tables?
schema-data-HREmployee-.txt
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
just select from the recursive CTE into a temp table
0
enrique_aeoAuthor Commented:
code in T-SQL please
0
PortletPaulfreelancerCommented:
CTE's are designed with recursion in mind, so there is absolutely no good reason to avoid using that CTE. Then, like any select, you are free to place that output into a table.
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
      FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
      FROM EmpOrg_CTE AS parent
      JOIN HR.Employees AS child
      ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname

into #you_choose_this_name

FROM EmpOrg_CTE;

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

enrique_aeoAuthor Commented:
no show data
no-rows.png
0
John_VidmarCommented:
Employee/Manger listing, results placed in temporary-table #temp:
SELECT	emp.empid
,	emp.mgrid
,	Employee	= emp.lastname + ', ' + emp.firstname 
,	Manager		= mgr.lastname + ', ' + mgr.firstname 
INTO	#temp
FROM	Employees	emp
JOIN	Employees	mgr	ON	emp.mgrid = mgr.empid

Open in new window

0
PortletPaulfreelancerCommented:
from that point onward you select from the temp table

e.g.
select * from #whatever_you_call_it

& I did actually mean that you should choose a table name.
0
enrique_aeoAuthor Commented:
there any advantage if I put the result into a temporary table
0
PortletPaulfreelancerCommented:
Are there advantages when using a temp table?
depends on what you are doing overall.
0
enrique_aeoAuthor Commented:
please,

complete this code

SELECT      emp.empid,      emp.mgrid,      emp.lastname + ', ' + emp.firstname,      Manager            = mgr.lastname + ', ' + mgr.firstname
--INTO      #temp
FROM      HR.Employees      emp
JOIN      HR.Employees      mgr      ON      emp.mgrid = mgr.empid
WHERE emp.empid = 5;

must leave the head and all employees (like example CTE)
0
John_VidmarCommented:
The CTE example is more powerful because it is recursive.  You need recursion if you have many levels of hierarchy (i.e., a manager manages several managers, those managers manage several managers, ..., who manage employees).

If you have a simple relationship, like you are either an employee or a manager, and you don't want to eliminate the managers then use an outer-join.  In this example, if the employee has a NULL in the manager fields, then they are a manager:
SELECT	emp.empid
,	emp.mgrid
,	Employee	= emp.lastname + ', ' + emp.firstname 
,	Manager		= mgr.lastname + ', ' + mgr.firstname 
--INTO	#temp
FROM	HR.Employees	emp
LEFT
JOIN	HR.Employees	mgr	ON	emp.mgrid = mgr.empid
WHERE	emp.empid = 5;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.