How to create and view the results of a temp table from a hierarchical (recursive) query in SQL Server 2008 R2

Question

Good Day.

I am trying to generate a temporary table after I get results from a hierarchical query, and then view those results, all within the same query (so the recurvise query gets the results I want from a table, then the CREATE TABLE query displays those results).

The recursive gets me the results I want, the issue I am having is in getting the query to display those results. I'm trying to build the temp table query after the recursive. However, the data return is only showing me the results of the recursive.

Thank you for any assistance!

The query:

WITH orgCode_hierarchy (ParentOrgCodeID, OrgCodeID, OrgCode, OrgName, OrgCodeSortOrder, LEVEL) AS
(
-- Initializing:
      SELECT h_base.ParentOrgCodeID, h_base.OrgCodeID, h_base.OrgCode, h_base.OrgName, cast('::' + h_base.OrgCode  + '::' AS VARCHAR (200)) AS OrgCodeSortOrder, 0 AS LEVEL
      FROM L_OrgCode h_base
      WHERE h_base.OrgCode is not null

      UNION all

-- Executing recursive: 
      SELECT h_child.ParentOrgCodeID, h_child.OrgCodeID, h_child.OrgCode, h_child.OrgName,  cast (ho.OrgCodeSortOrder + CASE WHEN left(h_child.OrgCode, 2) = '::' THEN '::' ELSE '' END  + h_child.OrgCode + '::' AS VARCHAR (200)) AS OrgCodeSortOrder, LEVEL + 1 AS LEVEL
      FROM L_OrgCode h_child
      inner join orgCode_hierarchy ho on h_child.ParentOrgCodeID = ho.OrgCodeID
)     

-- CTE:
SELECT DISTINCT ParentOrgCodeID, OrgCodeID, OrgCode, OrgName, OrgCodeSortOrder, LEVEL
FROM orgCode_hierarchy
WHERE OrgCode LIKE '2.2.1.1%'  
-- ORDER BY 1  
ORDER BY OrgCode, 1 

--Create temp tbl for the name/org code search
    CREATE TABLE #OrgCodesHier
    (
        OrgCodeID int, 
        OrgCode_Level int,
        OrgName varchar(255),
        OrgCode varchar(50),
        ParentOrgCodeID varchar(50)

        PRIMARY KEY (OrgCode) 
    )

    INSERT INTO #OrgCodesHier (OrgCodeID, OrgCode_Level, OrgName, OrgCode, ParentOrgCodeID)

    SELECT * FROM #OrgCodesHier

Answer 1

If I understood correctly...

    --Create temp tbl for the name/org code search
        CREATE TABLE #OrgCodesHier
        (
            OrgCodeID int, 
            OrgCode_Level int,
            OrgName varchar(255),
            OrgCode varchar(50),
            ParentOrgCodeID varchar(50)

            PRIMARY KEY (OrgCode) 
        )

    ;WITH orgCode_hierarchy (ParentOrgCodeID, OrgCodeID, OrgCode, OrgName, OrgCodeSortOrder, LEVEL) AS
    (
    -- Initializing:
          SELECT h_base.ParentOrgCodeID, h_base.OrgCodeID, h_base.OrgCode, h_base.OrgName, cast('::' + h_base.OrgCode  + '::' AS VARCHAR (200)) AS OrgCodeSortOrder, 0 AS LEVEL
          FROM L_OrgCode h_base
          WHERE h_base.OrgCode is not null

          UNION all

    -- Executing recursive: 
          SELECT h_child.ParentOrgCodeID, h_child.OrgCodeID, h_child.OrgCode, h_child.OrgName,  cast (ho.OrgCodeSortOrder + CASE WHEN left(h_child.OrgCode, 2) = '::' THEN '::' ELSE '' END  + h_child.OrgCode + '::' AS VARCHAR (200)) AS OrgCodeSortOrder, LEVEL + 1 AS LEVEL
          FROM L_OrgCode h_child
          inner join orgCode_hierarchy ho on h_child.ParentOrgCodeID = ho.OrgCodeID
    )     

    -- CTE:
INSERT INTO #OrgCodesHier (ParentOrgCodeID,OrgCodeID,OrgCode,OrgName,OrgCode_Level)
    SELECT DISTINCT ParentOrgCodeID, OrgCodeID, OrgCode, OrgName, LEVEL
    FROM orgCode_hierarchy
    WHERE OrgCode LIKE '2.2.1.1%'  
    -- ORDER BY 1  
    ORDER BY OrgCode, 1 


    SELECT * FROM #OrgCodesHier