需求：编写递归存储过程实现层级数据向TableC的插入

Got it, let's build this recursive stored procedure together. First, I'll start with realistic assumptions about your table schemas (since you didn't share exact definitions) — adjust these to match your actual data models:

Assumed Table Structures #

-- TableA: Main data store for core entities
CREATE TABLE TableA (
    EntityID INT PRIMARY KEY,
    EntityName VARCHAR(100) NOT NULL,
    -- Add other main data fields (e.g., Description, Status) as needed
    CreatedDate DATETIME DEFAULT GETDATE()
);

-- TableB: Time-bound parent-child relationships (updates quarterly)
CREATE TABLE TableB (
    RelationshipID INT PRIMARY KEY,
    ParentEntityID INT NOT NULL,
    ChildEntityID INT NOT NULL,
    StartDate DATE NOT NULL, -- First day of the quarter this relationship is active
    EndDate DATE NOT NULL, -- Last day of the quarter this relationship is active
    FOREIGN KEY (ParentEntityID) REFERENCES TableA(EntityID),
    FOREIGN KEY (ChildEntityID) REFERENCES TableA(EntityID)
);

-- TableC: Hierarchical storage for active relationship snapshots
CREATE TABLE TableC (
    HierarchyID INT IDENTITY(1,1) PRIMARY KEY,
    EntityID INT NOT NULL,
    ParentEntityID INT NULL, -- NULL = root level entity
    EntityName VARCHAR(100) NOT NULL,
    Level INT NOT NULL, -- Depth in hierarchy (1 = root, 2 = child, etc.)
    HierarchyPath VARCHAR(500) NOT NULL, -- Traceable path (e.g., "1/4/7")
    EffectiveDate DATE NOT NULL, -- Quarter/date this hierarchy snapshot is for
    FOREIGN KEY (EntityID) REFERENCES TableA(EntityID),
    FOREIGN KEY (ParentEntityID) REFERENCES TableA(EntityID)
);

Recursive Stored Procedure Implementation #

We'll use a recursive CTE to traverse active parent-child relationships for a specified target date, join with TableA to pull core entity data, and insert the full hierarchy into TableC. The procedure accepts a target date to filter only relationships valid for that quarter.

CREATE PROCEDURE InsertHierarchyIntoTableC
    @TargetDate DATE -- Pass the last day of your target quarter (e.g., '2024-06-30' for Q2 2024)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        -- Optional: Clear existing records for the target date to avoid duplicates
        -- Uncomment if you want a fresh snapshot each run
        -- DELETE FROM TableC WHERE EffectiveDate = @TargetDate;

        -- Recursive CTE to build the full active hierarchy
        WITH RecursiveHierarchy AS (
            -- Anchor member: Grab all root entities (no active parent relationship)
            SELECT 
                a.EntityID,
                CAST(NULL AS INT) AS ParentEntityID,
                a.EntityName,
                1 AS Level,
                CAST(a.EntityID AS VARCHAR(500)) AS HierarchyPath,
                @TargetDate AS EffectiveDate
            FROM TableA a
            LEFT JOIN TableB b 
                ON a.EntityID = b.ChildEntityID 
                AND @TargetDate BETWEEN b.StartDate AND b.EndDate
            WHERE b.RelationshipID IS NULL -- No active parent = root node

            UNION ALL

            -- Recursive member: Traverse child nodes linked to active parents
            SELECT 
                a.EntityID,
                rh.EntityID AS ParentEntityID,
                a.EntityName,
                rh.Level + 1 AS Level,
                CONCAT(rh.HierarchyPath, '/', a.EntityID) AS HierarchyPath,
                @TargetDate AS EffectiveDate
            FROM TableA a
            INNER JOIN TableB b 
                ON a.EntityID = b.ChildEntityID 
                AND @TargetDate BETWEEN b.StartDate AND b.EndDate
            INNER JOIN RecursiveHierarchy rh 
                ON b.ParentEntityID = rh.EntityID
        )

        -- Insert the complete hierarchy into TableC
        INSERT INTO TableC (EntityID, ParentEntityID, EntityName, Level, HierarchyPath, EffectiveDate)
        SELECT EntityID, ParentEntityID, EntityName, Level, HierarchyPath, EffectiveDate
        FROM RecursiveHierarchy;

        COMMIT TRANSACTION;
        PRINT 'Hierarchy snapshot inserted successfully for date: ' + CONVERT(VARCHAR, @TargetDate);
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Error inserting hierarchy: ' + ERROR_MESSAGE();
        THROW; -- Re-throw error for upstream error handling
    END CATCH
END

Key Customization & Explanations #

• Anchor Member: Identifies root entities by checking which TableA records aren't listed as children in any active TableB relationship for the target date.
• Recursive Member: Iteratively finds child nodes for each parent in the current hierarchy, only including relationships valid on the target date. It builds a traceable path and increments the hierarchy level for each child.
• Target Date Parameter: Lets you specify exactly which quarter's relationships to use — pass the last day of the quarter to capture all active relationships for that period.
• Duplicate Prevention: The commented DELETE statement clears existing snapshots for the target date. Uncomment this if you want a clean snapshot each time you run the procedure.

Best Practices #

• Index Optimization: Speed up recursive joins by adding indexes to TableB's key fields:

  CREATE NONCLUSTERED INDEX IX_TableB_Child_Date ON TableB (ChildEntityID, StartDate, EndDate);
  CREATE NONCLUSTERED INDEX IX_TableB_Parent_Date ON TableB (ParentEntityID, StartDate, EndDate);
  

• Recursion Depth: SQL Server defaults to a 100-level recursion limit. If your hierarchy is deeper, add OPTION (MAXRECURSION 0) at the end of the CTE query (0 = no limit, use cautiously to avoid infinite loops).
• Testing: Validate with a small dataset first — check the HierarchyPath and Level fields to ensure the recursion is building the hierarchy correctly.
• Transaction Safety: The procedure uses transactions to roll back all changes if any step fails, preventing partial hierarchy inserts.

内容的提问来源于stack exchange，提问作者S_developer