如何高效向邻接表插入层级数据？

问题背景 #

现有层级表结构如下：

CREATE TABLE tmp.myTable  
(  
    [Id] int IDENTITY(1,1) PRIMARY KEY ,      
    [Desc] nvarchar(50) NOT NULL,
    [Lvl] TINYINT NOT NULL,
    [ParentId] int REFERENCES tmp.myTable(Id)
)

需要插入层级结构（如 A > AB > ABC > ABCD），当前采用逐次插入的方式，每次依赖前一条数据的Id：

INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId]) VALUES('A',1,NULL);

INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
SELECT 'AB',2,Id FROM tmp.myTable WHERE [Desc]='A'

INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
SELECT 'ABC',3,Id FROM tmp.myTable WHERE [Desc]='AB'

INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
SELECT 'ABCD',4,Id FROM tmp.myTable WHERE [Desc]='ABC'

这种方式在网络缓慢或VPN环境下耗时极高，曾考虑将Id改为非IDENTITY类型由应用端控制，但存在多用户并发插入时事务失败、ParentId错误的问题，寻求更优实现方式。

优化方案 #

方案1：表变量+OUTPUT子句减少网络交互 #

通过表变量暂存待插入的层级数据，结合OUTPUT子句捕获每条插入数据的自增Id，将所有插入逻辑封装在一个批处理中，只需要1次网络往返即可完成全部层级插入，彻底解决多次请求的耗时问题。

示例代码：

DECLARE @HierarchyData TABLE (Seq INT, [Desc] NVARCHAR(50), Lvl TINYINT, ParentSeq INT);
DECLARE @InsertedIds TABLE (Seq INT, Id INT);

-- 预存所有层级数据，用Seq关联父子关系
INSERT INTO @HierarchyData (Seq, [Desc], Lvl, ParentSeq)
VALUES (1, 'A', 1, NULL),
       (2, 'AB', 2, 1),
       (3, 'ABC', 3, 2),
       (4, 'ABCD', 4, 3);

-- 插入根节点并捕获其ID
INSERT INTO tmp.myTable ([Desc], Lvl, ParentId)
OUTPUT 1, inserted.Id INTO @InsertedIds(Seq, Id)
SELECT [Desc], Lvl, ParentId FROM @HierarchyData WHERE Seq = 1;

-- 循环插入子节点，利用已捕获的父ID
DECLARE @CurrentSeq INT = 2;
WHILE @CurrentSeq <= (SELECT MAX(Seq) FROM @HierarchyData)
BEGIN
    INSERT INTO tmp.myTable ([Desc], Lvl, ParentId)
    OUTPUT @CurrentSeq, inserted.Id INTO @InsertedIds(Seq, Id)
    SELECT hd.[Desc], hd.Lvl, ii.Id
    FROM @HierarchyData hd
    JOIN @InsertedIds ii ON hd.ParentSeq = ii.Seq
    WHERE hd.Seq = @CurrentSeq;

    SET @CurrentSeq = @CurrentSeq + 1;
END

该方案保留了IDENTITY列的自动自增特性，完全由数据库维护ID唯一性，不会出现并发冲突问题。

方案2：递归CTE+MERGE一次性插入（适用于有规律的层级） #

如果层级结构有明确的生成规律（如示例中前缀递进），可以用递归CTE生成完整的层级数据集，再通过MERGE结合OUTPUT关联父子ID，一次性完成插入操作。

示例代码：

DECLARE @RootDesc NVARCHAR(50) = 'A';
DECLARE @MaxLevel INT = 4;

WITH HierarchyCTE AS (
    -- 定义根节点
    SELECT 
        [Desc] = @RootDesc,
        Lvl = 1,
        ParentId = CAST(NULL AS INT)
    UNION ALL
    -- 递归生成子节点（这里按末尾加B的规律生成，可根据实际调整）
    SELECT 
        [Desc] = hc.[Desc] + 'B',
        Lvl = hc.Lvl + 1,
        ParentId = CAST(0 AS INT) -- 占位，后续更新为父节点ID
    FROM HierarchyCTE hc
    WHERE hc.Lvl < @MaxLevel
),
-- 插入所有节点并捕获自增ID
InsertedData AS (
    MERGE INTO tmp.myTable t
    USING HierarchyCTE hc ON 1=0 -- 无条件插入所有数据
    WHEN NOT MATCHED THEN
        INSERT ([Desc], Lvl, ParentId)
        VALUES (hc.[Desc], hc.Lvl, CASE WHEN hc.Lvl = 1 THEN NULL ELSE 0 END)
        OUTPUT inserted.Id, inserted.Lvl, inserted.[Desc]
)
-- 更新子节点的ParentId为对应父节点的ID
UPDATE t
SET t.ParentId = parent.Id
FROM tmp.myTable t
JOIN InsertedData child ON t.[Desc] = child.[Desc]
JOIN InsertedData parent ON child.Lvl = parent.Lvl + 1 AND child.[Desc] LIKE parent.[Desc] + '%';

注意：此方案仅适用于层级结构有固定生成规则的场景，无规律的自定义层级建议用方案1。

方案3：SEQUENCE替代IDENTITY（兼顾应用端控制与并发安全） #

如果希望保留应用端对ID的规划能力，同时解决并发冲突问题，可以用数据库的SEQUENCE对象替代IDENTITY列。SEQUENCE是数据库级别的自增序列，多用户并发时会自动分配唯一ID，避免冲突。

首先修改表结构：

-- 创建自增序列
CREATE SEQUENCE tmp.HierarchyIdSeq
    START WITH 1
    INCREMENT BY 1;

-- 修改表结构，用SEQUENCE生成ID
CREATE TABLE tmp.myTable  
(  
    [Id] int PRIMARY KEY DEFAULT NEXT VALUE FOR tmp.HierarchyIdSeq,      
    [Desc] nvarchar(50) NOT NULL,
    [Lvl] TINYINT NOT NULL,
    [ParentId] int REFERENCES tmp.myTable(Id)
)

然后可以在数据库端或应用端一次性获取所需ID，关联父子关系后批量插入：

-- 数据库端示例：一次性生成ID并插入所有层级
DECLARE @Ids TABLE (Seq INT, Id INT);
INSERT INTO @Ids (Seq, Id)
VALUES (1, NEXT VALUE FOR tmp.HierarchyIdSeq),
       (2, NEXT VALUE FOR tmp.HierarchyIdSeq),
       (3, NEXT VALUE FOR tmp.HierarchyIdSeq),
       (4, NEXT VALUE FOR tmp.HierarchyIdSeq);

INSERT INTO tmp.myTable ([Desc], Lvl, ParentId)
SELECT hd.[Desc], hd.Lvl, parent.Id
FROM (
    VALUES (1, 'A', 1, NULL),
           (2, 'AB', 2, 1),
           (3, 'ABC', 3, 2),
           (4, 'ABCD', 4, 3)
) hd(Seq, [Desc], Lvl, ParentSeq)
LEFT JOIN @Ids parent ON hd.ParentSeq = parent.Seq;

这种方式既允许提前规划父子ID的关联关系，又由数据库保证ID的唯一性，彻底解决并发场景下的事务失败和ParentId错误问题。

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