?
Solved

CTE or temporary tables

Posted on 2014-04-11
10
Medium Priority
?
315 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 49

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1332 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1332 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 49

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 668 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 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