主页 / user-199878

Jacob Stamm's questions

Martin Hope
Jacob Stamm
Asked: 2020-01-30 09:17:48 +0800 CST
  • 0

想象一个包含三列的表：一个 ID 主键、一个可以为空的 ParentID 引用同一表上的 ID 列，以及一个 TreeID 来指示组成特定树的记录。一个基本的层次结构可能看起来像这样。请注意，序数值不一定会影响记录在层次结构中的位置。

      dbo.TreeNodes             Visual representation:
+----+----------+--------+    --------------------------
| ID | ParentID | TreeID |       Tree 7:     Tree 15:
+----+----------+--------+       1           10
|  1 | NULL     |      7 |       |–3         |–9
|  2 | 4        |      7 |       |–4
|  3 | 1        |      7 |       |–|–2
|  4 | 1        |      7 |       |-5
|  5 | 1        |      7 |
|  9 | 10       |     15 |
| 10 | NULL     |     15 |
+----+----------+--------+

给定另一个具有相同定义的不相关表（dbo.TreeNodes2假设为 ），我发现将数据从第一个表复制到第二个表同时保持其自引用层次结构并不是那么简单。当需要填充 ParentID 值时，我在插入新的 dbo.TreeNodes2 记录后卡住了。我必须有某种映射表才能知道第一个表中的哪些记录映射到第二个表中的新记录。这是我到目前为止所得到的：

DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

我可以向 dbo.TreeNodes2添加一个不相关的OldID列以将原始 ID 复制到其中，然后我可以使用它来创建一个映射以加入并填充新的 ParentID 记录，然后OldID在完成后将所有值清空，但这似乎是糟糕的设计。如果 SQL Server 允许 INSERT 的 OUTPUT 子句包含来自连接的列，那么这很容易，但是所有文档以及我自己的测试都表明只有Inserted.*列、Deleted.*列和标量值/文字可以是在 OUTPUT 子句中使用。什么都没有加入。

我的梦想，执行我想要的非有效 T-SQL 看起来像这样：

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

从这里，我将有一个映射表，我可以使用它来填充新表中的 ParentID 值。

是否有一种模式可以处理这些类型的跨表分层插入，而不依赖于唯一目的是允许迁移的额外列，之后它是一个不相关的列？

sql-server hierarchy