CTE to temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE to temp table

Author

Topic

sql_msg
Starting Member

13 Posts

Posted - 2014-11-25 : 01:49:35

Hello All.
I have a CTE here,but i need to convert this into some other object as i am not interested to use this, we can use temp table or loop to derive the same functionality,could you pls help me to get that.

WITH Vcte (ParentMPNID,ChildMPNID,ParentVOrgID)
AS (
SELECT GPR1.ParentMPNID AS ParentMPNID
, GPR1.ChildMPNID AS ChildMPNID
, GPR1.ParentMPNID AS ParentVOrgID

FROM dml.ExtGMOPartnerOrganizationRelationMap GPR1
Where GPR1.ParentOrgType = 'Virtual Organization'
UNION ALL
SELECT GPR2.ParentMPNID AS ParentMPNID
, GPR2.ChildMPNID AS ChildMPNID
, RC.ParentVOrgID AS ParentVOrgID

FROM dml.ExtGMOPartnerOrganizationRelationMap GPR2
INNER JOIN Vcte RC
ON RC.ChildMPNID = GPR2.ParentMPNID
)
SELECT PartnerMPNID, VORGMPNID
FROM
(
SELECT
ParentMPNID AS PartnerMPNID
, ParentVOrgID AS VORGMPNID
FROM Vcte

UNION

SELECT
ChildMPNID AS PartnerMPNID
, ParentVOrgID AS VORGMPNID
FROM Vcte
) DT

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-25 : 08:55:49

[code]
SELECT GPR1.ParentMPNID AS ParentMPNID
, GPR1.ChildMPNID AS ChildMPNID
, GPR1.ParentMPNID AS ParentVOrgID
INTO #Vcte
FROM dml.ExtGMOPartnerOrganizationRelationMap GPR1
WHERE GPR1.ParentOrgType = 'Virtual Organization'
UNION ALL
SELECT GPR2.ParentMPNID AS ParentMPNID
, GPR2.ChildMPNID AS ChildMPNID
, RC.ParentVOrgID AS ParentVOrgID
FROM dml.ExtGMOPartnerOrganizationRelationMap GPR2
INNER JOIN Vcte RC
ON RC.ChildMPNID = GPR2.ParentMPNID

SELECT PartnerMPNID
, VORGMPNID
FROM(
SELECT ParentMPNID AS PartnerMPNID
, ParentVOrgID AS VORGMPNID
FROM #Vcte
UNION
SELECT ChildMPNID AS PartnerMPNID
, ParentVOrgID AS VORGMPNID
FROM #Vcte)DT
[/code]

sql_msg
Starting Member

13 Posts

Posted - 2014-11-25 : 14:15:27

I am having error here

Msg 2770, Level 16, State 1, Line 12
The SELECT INTO statement cannot have same source and destination tables.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 10:49:05

Sorry, I missed that your CTE is recursive. There is no way to do this without a CTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources