Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
enrique_aeo
Asked:
enrique_aeo
  • 4
  • 4
  • 2
3 Solutions
 
PortletPaulCommented:
just select from the recursive CTE into a temp table
0
 
enrique_aeoAuthor Commented:
code in T-SQL please
0
 
PortletPaulCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now