Solved

CTE or temporary tables

Posted on 2014-04-11
10
294 Views
Last Modified: 2014-04-17
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
Comment
Question by:enrique_aeo
  • 4
  • 4
  • 2
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39995377
just select from the recursive CTE into a temp table
0
 

Author Comment

by:enrique_aeo
ID: 39995416
code in T-SQL please
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 333 total points
ID: 39995428
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
 

Author Comment

by:enrique_aeo
ID: 39995432
no show data
no-rows.png
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39995436
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 333 total points
ID: 39995437
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
 

Author Comment

by:enrique_aeo
ID: 39995478
there any advantage if I put the result into a temporary table
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39997054
Are there advantages when using a temp table?
depends on what you are doing overall.
0
 

Author Comment

by:enrique_aeo
ID: 39999033
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
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 167 total points
ID: 40004604
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now