CODEWITHSUNDEEP
sql-serverrecursion
TheyMadeMeTheDBA
TheyMadeMeTheDBA

Reputation: 31

structure this data so it can be read by a recursive cte? (SQL Server)

I need a query that will return the ancestry of a table given the information below. The source table is currently structured as described, and a recursive CTE doesn't work. I can't seem to wrap my mind around how the source table should be structured to make a CTE work.

Can someone please suggest a source table structure and query that will return the following? If there's a better way than recursion, that works too.

The source table contains information from my SQL Server database that reflects data lineage. In order to produce table T4, you'd execute procedures P1, P2, and P3.

There is a rule that a table can have only one 'parent' procedure, but a procedure can build multiple tables. So P3 can build T3 and T4, but T3 can only be built by one procedure (P3).

EXAMPLE:

If the query is fed 'T4', it should return this information:

referencing_ancestor    referenced_ancestors    
P3                      T2, LOOKUP_TABLE
P2                      T1
P1                      staging

This is the source information in its current structure, but the structure can change. I just need the ancestry information of a given table.

declare @Dependencies table
(
    id int identity(1,1),
    referencing_name nvarchar(50) NOT NULL,
    referenced_name nvarchar(50) NULL,
    select_from int NULL,
    insert_to int NULL
) 

insert into @Dependencies
select 'P1', 'staging', 1, 0    --> P1 selects data from staging
union all
select 'P1', 'T1', 0, 1         --> P1 populates T1
union all
select 'P2', 'T1', 1, 0         --> P2 selects data from T1
union all
select 'P2', 'T2', 0, 1         --> P2 populates T2
union all
select 'P3', 'LOOKUP_TABLE', 1, 0  --> P3 selects data from LOOKUP_TABLE
union all
select 'P3', 'T2', 1, 0         --> P3 selects data from T2
union all
select 'P3', 'T3', 0, 1         --> P3 populates  T3
union all
select 'P3', 'T4', 0, 1         --> P3 populates T4

This query doesn't work, not sure how to fix:

 ;with ancestors as 
    (
        select referencing_name, referenced_name, Level = 0
        from @Dependencies
        where referenced_name = 'T4'

        union all

        select d.referencing_name, d.referenced_name, Level + 1
        from @Dependencies d
        inner join ancestors a on a.referenced_name = d.referenced_name
        where insert_to = 0
    )

    select * from ancestors

Upvotes: 3

Views: 54

Answers (1)

Shoeless
Shoeless

Reputation: 678

I think you might want to establish some sort of "level" column in the @Dependencies table itself, but in this example I simply ordered by the referencing_name in the DENSE_RANK function.

;WITH ancestors AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY referencing_name) AS tbl_level
    FROM    @Dependencies
)
SELECT  a2.* 
FROM    ancestors a1
JOIN    ancestors a2 ON a2.tbl_level <= a1.tbl_level
WHERE   a1.referenced_name = 'T4'
AND     a2.insert_to = 0 
ORDER BY tbl_level DESC

Upvotes: 1

Related Questions