enrique_aeo
asked on
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
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
PortletPaul
just select from the recursive CTE into a temp table
ASKER
code in T-SQL please
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no show data
no-rows.png
no-rows.png
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.empidSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
there any advantage if I put the result into a temporary table
Are there advantages when using a temp table?
depends on what you are doing overall.
depends on what you are doing overall.
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.