SQL Server HierarchyID:如何在不知道子项ID的情况下添加子项？

Question

我需要从表变量@Toc向表PE_TableOfContents插入内容，该表有一个HIERARCHYID。在PE_TableOfContents中将有一个根项目。从这个根项目中，我可以获得根HIERARCHYID (@rootNode)。

但是，当我插入子列时，我不确定如何创建新的HIERARCHYID，因为在插入列之前，我不知道该列的ID。

DECLARE @rootNode HIERARCHYID = (SELECT tocNode from PE_TableOfContents WHERE TocNodeLevel = 0)

INSERT INTO PE_TableOfContents (
    TocNode,
    ... all of the other columns)
SELECT 
    @rootNode.ToString + ???? + '/',
    T... all of the other columns
FROM 
    @Toc T

我正在浏览hierarchyid的所有文档，也许我遗漏了什么，但我没有看到这个。

Answer 1

从HierarchyID的文档中看不出来的一件事是，您可以自由地指定您想要的任何数字数据作为路径的组成部分。我个人喜欢使用自动生成的ID (无论是标识值还是其他值)。我还喜欢将诸如ParentID之类的内容作为表中的一个列，以便在保持hierarchyid列最新的过程中出现问题时，可以使用递归查询重新生成它(也就是说，hierarchyid列是派生数据，其存在的目的只是为了提高查询性能)。

更明确地说，假设我有下表：

ID      ParentID
================
100       NULL
200       100
300       100
400       200

我会像这样派生hierarchyid列：

ID      ParentID   h
=========================
100       NULL     /100/
200       100      /100/200
300       100      /100/300
400       200      /100/200/400

如果您使用的是标识列，我想不出一种方法来做到这一点，首先插入带有hierarchyid的null值的行。如果使用序列来分配ID，则可以从序列中获取一个值，并将其用于ID值和派生hierarchyid。

Answer 2

好了，经过足够的谷歌搜索，我found a good explanation。

首先，获取根(或父)节点：

DECLARE @rootNode HIERARCHYID = (SELECT tocNode FROM PE_TableOfContents WHERE TocNodeLevel = 0)

然后，获取该根(或父)节点的最后一个已知子节点：

DECLARE @lastNode HIERARCHYID = (SELECT MAX(tocNode) FROM PE_TableOfContents WHERE tocNode.GetAncestor(1) = @rootNode);

然后按如下所示进行插入：

INSERT INTO PE_TableOfContents (
    TocNode,
    ... all of the other columns)
SELECT         
    @rootNode.GetDescendant(@lastNode, NULL),
    T... all of the other columns
FROM 
    @Toc T