如何用T-SQL从零插入深度为X的递归表负载测试数据？

1. 创建匹配的表结构 #

先把你提供的C#类转换成对应的T-SQL表（如果还未创建）：

CREATE TABLE dbo.RecursiveTable (
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    ParentId UNIQUEIDENTIFIER NOT NULL
);

2. 从零生成指定深度的递归数据 #

通过递归CTE直接生成并插入数据，无需初始数据支撑。只需调整@MaxDepth的值即可控制递归深度X，同时可自定义每个父节点的子节点数量：

DECLARE @MaxDepth INT = 5; -- 设置目标递归深度X
DECLARE @ChildNodesPerParent INT = 3; -- 每个父节点生成的子节点数量，按需调整

WITH NumberSeed AS (
    -- 生成指定数量的种子值，用来控制每个父节点的子节点数
    SELECT TOP (@ChildNodesPerParent) Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns
),
RecursiveCTE AS (
    -- 锚点：生成根节点（深度1）
    SELECT 
        Id = NEWID(),
        Name = 'Level 1 - Node 1',
        ParentId = '00000000-0000-0000-0000-000000000000', -- 根节点无父节点，用空Guid标识，可按业务规则调整
        CurrentDepth = 1
    UNION ALL
    -- 递归：生成各级子节点，直到达到最大深度
    SELECT 
        Id = NEWID(),
        Name = 'Level ' + CAST(rc.CurrentDepth + 1 AS NVARCHAR) + ' - Node ' + CAST(ns.Num AS NVARCHAR),
        ParentId = rc.Id,
        CurrentDepth = rc.CurrentDepth + 1
    FROM RecursiveCTE rc
    CROSS JOIN NumberSeed ns
    WHERE rc.CurrentDepth < @MaxDepth
)
INSERT INTO dbo.RecursiveTable (Id, Name, ParentId)
SELECT Id, Name, ParentId
FROM RecursiveCTE;

3. 适配反复增删的测试场景 #

如果需要频繁执行增删操作，可把生成逻辑封装成存储过程，方便重复调用：

CREATE PROCEDURE GenerateRecursiveTestData
    @MaxDepth INT,
    @ChildNodesPerParent INT
AS
BEGIN
    SET NOCOUNT ON;
    -- 快速清空表，用于每次生成全新数据
    TRUNCATE TABLE dbo.RecursiveTable;

    WITH NumberSeed AS (
        SELECT TOP (@ChildNodesPerParent) Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM sys.all_columns
    ),
    RecursiveCTE AS (
        SELECT 
            Id = NEWID(),
            Name = 'Level 1 - Node 1',
            ParentId = '00000000-0000-0000-0000-000000000000',
            CurrentDepth = 1
        UNION ALL
        SELECT 
            Id = NEWID(),
            Name = 'Level ' + CAST(rc.CurrentDepth + 1 AS NVARCHAR) + ' - Node ' + CAST(ns.Num AS NVARCHAR),
            ParentId = rc.Id,
            CurrentDepth = rc.CurrentDepth + 1
        FROM RecursiveCTE rc
        CROSS JOIN NumberSeed ns
        WHERE rc.CurrentDepth < @MaxDepth
    )
    INSERT INTO dbo.RecursiveTable (Id, Name, ParentId)
    SELECT Id, Name, ParentId
    FROM RecursiveCTE;
END;

调用示例 #

-- 生成深度为6、每个父节点带4个子节点的测试数据
EXEC GenerateRecursiveTestData @MaxDepth = 6, @ChildNodesPerParent = 4;

-- 快速清空数据
TRUNCATE TABLE dbo.RecursiveTable;

内容的提问来源于stack exchange，提问作者Julian Wen Hsi Lee