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

Christian Phillips · Accepted Answer · 2013-06-13T18:21:40.747

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

edited Jun 13 '13 at 18:21

answered Jun 12 '13 at 18:52

Christian Phillips

18,399
8
53
82

Thank you for the response! I tried rearranging as per your suggested example above, however it invalidated all of my column references. Trying to determine why but no joy thus far. – WCS Jun 12 '13 at 19:21
the order of the insert needs changing - I just used the order you gave – Christian Phillips Jun 12 '13 at 19:24
I have edited, but you are missing a column 'OrgCodeSortOrder' – Christian Phillips Jun 12 '13 at 19:27
Just a quick update - still working the problem. I tried arranging the columns in the correct oreder and added the OrgCodeSortOrder, but no joy. Ut keeps breaking first on the `WITH` statement ( Incorrect syntax near 'orgCode_hierarchy'. Expecting '('. ) and then immediately after that in the `WITH` the ParentOrgCodeID is looking for a '(' or `SELECT`. Everything else is broken with these issues or "invalid object name" EXCEPT the `SELECT` statement in the recursive section - everything is good in that section until `cast (ho.NegCodeSortOrder` – WCS Jun 13 '13 at 18:11
I will continue to work different things so that I can formulate more intelligent questions. – WCS Jun 13 '13 at 18:12
I have just added a semi colon in front of with – Christian Phillips Jun 13 '13 at 18:21
Ah, that did the trick exactly. Thank you for working with me on this, it is much appreciated. – WCS Jun 14 '13 at 13:45

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

1 Answers1