主页 / dba / 问题 / 258446
Accepted
Jacob Stamm
Asked: 2020-01-30 09:17:48 +0800 CST

将分层数据从一个表复制到另一个表的模式？

  • 772

想象一个包含三列的表：一个 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

1 个回答

  1. Best Answer

    在这种情况下，我不会将 IDENTITY 用于主键，而是切换到序列。
    这样，您可以将序列号保留到临时表中，该表成为您的映射表，例如：

    CREATE SEQUENCE TreeNodes2Sequence START WITH 1 INCREMENT BY 1 ; 

CREATE TABLE TreeNodes2 (
    ID int PRIMARY KEY DEFAULT (NEXT VALUE FOR TreeNodes2Sequence),
    ...
)

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

INSERT INTO #InsertedRows
(
    Id,
    OldId
)
SELECT
    NEXT VALUE FOR TreeNodes2Sequence,
    tn1.ID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

    然后使用此临时表插入所有父级为空的值，然后更新父级。

    • 1

相关问题