复制邻接模型
我需要创建一个 sql 存储过程（Sql Server 2008 - T-SQL），它复制邻接模型中的节点。
表可以看作有两列，Id 和 ParentId（FK 到 Id）。复制意味着所有下属也需要复制。
我认为使用WITH是一个好的开始，但我很好奇是否可以在不使用游标的情况下完成此复制。
I need to create a sql stored procedure (Sql Server 2008 - T-SQL) which copies a node in an adjacency model.
Table can be seen as having two columns, Id and ParentId (FK to Id). Copying means that also all subordinates need to be copied.
I think that using WITH is a good start, but I'm curious if I can do this copy without using Cursors.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
邻接列表的根本问题是 SQL 中没有通用的方法来提取整个子树，因此您已经遇到了在不借助游标的情况下识别需要复制的所有行的问题。
如果可能，将邻接列表迁移到嵌套集模型，这样您就可以轻松识别子树的所有节点。然而，对于一般的插入和删除，嵌套集合模型的维护更加复杂。
编辑：正如 'a_horse_with_no_name' 所指出的，一般 SQL 中有一种处理邻接列表、递归公用表表达式的方法。
The fundamental problem with adjacency lists is there is no general way in SQL to extract an entire sub tree, so you already have a problem of identifying all the rows you need to duplicate without resorting to a cursor.
If possible migrate your adjacency list to a nested set model which allows you to easily identify all the nodes of a subtree. However, the maintenance of a nested set model is more complex for general inserts and deletes.
EDIT: As pointed out by 'a_horse_with_no_name' there is a way in general SQL to process adjacency lists, recursive common table expressions.
复制整个子树有点问题，因为当您复制子树时，您要么
在任何一种情况下，您都会在某个时刻将数据拖入不一致的状态 - 这表明您的设计存在一些问题（例如您的记录是否需要有多个父项？如果是，那么您应该考虑重新设计）。
因此，您应该用更完整的示例来更新答案，说明您正在尝试做什么。
一种解决方案是使用临时表，选择插入应该不是问题，只是更新引用的 ID 才是问题。
因此，
该过程需要像这样进行，因为很难更改初始 WITH INSERT 中的 ID（记录 ID 和引用父级的 ID）。然而，如果有一个仅依赖于 max_id 或仅依赖于旧 ID 的好函数，这可能是可能的。
Copying a whole sub-tree is a bit of a problem because when you copy your sub-tree you are either
In either case you are dragging data through inconsistent state at some point - which indicates some problems with your design (for example do your records need to have multiple parents or not? if yes, then you should consider redesigning).
So, you should update the answer with a more complete example of what you are trying to do.
One solution would be to have a temporary table, selecting for the insert should not be a problem, it is just updating the referenced IDs that would be a problem.
So
The procedure needs to go like this because it would be hard to change the IDs (both record IDs and ID referring to parent) in initial WITH INSERT. However it might be possible, if there was a nice function that depended only on max_id or only on old IDs.