T-SQL：复制层次结构数据的最佳方法是什么？

Question

我的数据库如下所示：

Questionnaire 
Id 
Description

Category
id
description
QuestionnaireId (FK)    

Question
id
CategoryId (FK)
field

当我复制一个问卷时，我希望复制所有相关的表。这意味着问卷表将获得新的ID。然后，必须复制所有属于问卷的类别。因此，新插入的类别必须获得新的问卷ID。在类别之后，必须复制问题。但是类别ID必须更新为新插入的类别。

如何使用T-SQL实现此操作？

- Martijn

---

我并没有看到任何问题...只需复制数据并使用 SET IDENTITY_INSERT [table A] ON 插入，B和C同理。 - Jamiec

如果我在A表中插入一条记录，这条记录会获得一个ID，假设是104。然后，我必须在B表中插入一行，该行引用了A表，因此table_A_id必须相同：104。 - Martijn

只要你要复制到的地方是空的，就不应该有问题，因为使用IDENTITY_INSERT时，新数据将具有相同的PK。你试图复制到哪里？也许是另一个相同的数据库？ - Jamiec

我已更新我的帖子。希望现在表述更清晰了。我想要完整复制所有记录，这样新记录就不会引用源记录。 - Martijn

Answer 1

这很容易实现，但你需要随时跟踪所有内容。通常我会创建一个单独的存储过程来实现这个功能，该存储过程将复制的问卷作为输入参数。

  DECLARE @newQuestionnaireId INT
  INSERT INTO Questionnaire
  (Id,Description)
  SELECT Id, Description 
  FROM Questionnaire
  WHERE ID = @sourceQuestionnaireID
  SET @newquestionnaireId = SCOPE_IDENTITY()

现在您有一个新的标题记录以及复制生成的新Id。下一步是将类别加载到临时表中，该表具有新Id的额外字段。

DECLARE @tempCategories TABLE (id INT, description VARCHAR(50),newId INT)
INSERT INTO @tempCategories(id,description)
SELECT id, description FROM Category 
WHERE questionnaireId = @sourceQuestionnaireId

现在，您有一个包含所有要插入的类别及其对应新ID字段的临时表。使用游标遍历该列表并插入新记录，并使用类似的SCOPE_IDENTITY调用来填充新的ID。

DECLARE cuCategory CURSOR FOR SELECT Id, Description FROM @tempCategories
DECLARE @catId INT, @catDescription, @newCatId INT
OPEN cuCategory
FETCH NEXT FROM cuCategory INTO @catId,@catDescription
WHILE @@FETCH_STATUS<>0
BEGIN
  INSERT INTO Category(description,questionnaireId)
  VALUES(@catDescription,@newQuestionnaireId)
  SET @newCatId = SCOPE_IDENTITY()

  UPDATE @tempCategories SET newCatId=@newCatId
  WHERE id=@catId
  FETCH NEXT FROM cuCategory INTO @catId,@catDescription
END
CLOSE cuCategory
DEALLOCATE cuCategory

此时，您现在拥有一个临时表，该表将原始问卷中的catId映射到新问卷的catId。这可以用于以类似的方式填充最终表格 - 我会把它留给您作为练习，但如果您遇到困难，请随时在此处回复。

最后，我建议在事务内执行整个操作，以免在出现问题时出现未完成的副本。

一些免责声明：以上所有内容都是快速输入的，不要指望它能立即运行。其次，我假设您所有的PK都是标识字段，它们应该是！如果它们不是，请使用适当的逻辑替换SCOPE_IDENTITY（）调用以生成下一个ID。

编辑：可以在此处找到有关游标操作的文档。

- Jamiec

---

谢谢。这确实是我需要的。我看到了很多我从未接触过的新东西：CURSOR、FETCH NEXT FROM、FETCH_STATUS。你能稍微解释一下这些东西吗？ - Martijn

我已经为你几乎写好答案了，难道你不能将这几个短语输入到谷歌中吗？唉。 - Jamiec

Answer 2

我遇到了类似的问题，开始实施@Jamiec建议的解决方案，但很快就意识到我需要更好的解决方案，因为我的模型比这里引用的例子要大得多。我有一个主表和三个中间表，每个中间表都有一个或多个第三方表。而且这三个中间表每个都有大约50列。这将意味着在使用临时memvars的获取部分中，需要输入大量工作。我尝试找到一种直接将FETCH导入临时表的方法，但似乎无法做到这一点。 我所做的是在中间表中添加一个名为OriginalId的列。以下是我的代码，已翻译成提问者所使用的模型：

DECLARE @newQuestionnaireId INT
INSERT INTO Questionnaire (Id,Description)
SELECT Id, Description FROM Questionnaire
WHERE ID = @sourceQuestionnaireID
SET @newquestionnaireId = SCOPE_IDENTITY()

INSERT INTO Category(QuestionnaireId, description, originalId)
SELECT @newquestionnaireId, description, id FROM Category 
WHERE questionnaireId = @sourceQuestionnaireId

INSERT INTO Question SELECT Category.Id, Question.Field
FROM Question join Category on Question.CategoryId = Category.OriginalId
WHERE Category.QuestionnaireId = @newquestionnaireId

在我的模型中，所有的id字段都是Identities，因此在插入时无需提供它们。

还有一件事，在我放弃CURSOR方法之前我发现了另一个小技巧，通过使用具有BREAK的无限WHILE循环来避免输入FETCH语句两次。详情请参考这篇聪明的小技巧。

- Milton

Answer 3

以下是一种无需使用游标的方法，它依靠记住事件的顺序，然后利用该顺序来解析子节点。

Declare @Parrent TABLE( ID int PRIMARY KEY IDENTITY, Value nvarchar(50))
Declare @Child TABLE( ID int PRIMARY KEY IDENTITY, ParrentID int, Value nvarchar(50))

insert into @Parrent (Value) Values ('foo'),('bar'),('bob')
insert into @Child (ParrentID, Value) Values (1,'foo-1'),(1,'foo-2'),(2,'bar-1'),(2,'bar-2'),(3,'bob')

declare @parrentToCopy table (ID int) -- you can me this a collection
insert into @parrentToCopy values (2)

select * from @Parrent p inner join @Child c on p.ID = c.ParrentID order by p.ID asc, c.ID asc

DECLARE @Ids TABLE( nID INT);

INSERT INTO @Parrent (Value)
OUTPUT INSERTED.ID
INTO @Ids
SELECT
        Value
FROM @Parrent p
inner join @parrentToCopy pc on pc.ID=p.ID
ORDER BY p.ID ASC

INSERT INTO @Child (ParrentID, Value)
SELECT
       nID
       ,Value
FROM @Child c
inner join (select ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS 'RowNumber' from @parrentToCopy) o ON o.ID = c.ParrentID
inner join (select nID, ROW_NUMBER() OVER (ORDER BY nID ASC) AS 'RowNumber' from @Ids) n ON o.RowNumber = n.RowNumber

select * from @Parrent p inner join @Child c on p.ID = c.ParrentID order by p.ID asc, c.ID asc

完整的文章在这里 http://bashamer.wordpress.com/2011/10/04/copying-hierarchical-data-in-sql-server/

- Bas Hamer