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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PortletPaulEE Topic AdvisorCommented:
just select from the recursive CTE into a temp table
enrique_aeoAuthor Commented:
code in T-SQL please
PortletPaulEE Topic AdvisorCommented:
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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

enrique_aeoAuthor Commented:
no show data
no-rows.png
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

PortletPaulEE Topic AdvisorCommented:
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.
enrique_aeoAuthor Commented:
there any advantage if I put the result into a temporary table
PortletPaulEE Topic AdvisorCommented:
Are there advantages when using a temp table?
depends on what you are doing overall.
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)
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

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.