Sorry if the title is poorly descriptive, but I can't do better right now =(
对不起,如果标题描述性差,但我现在不能做得更好=(
So, I have this master-detail scheme, with the detail being a tree structure (one to many self relation) with n levels (on SQLServer 2005)
所以,我有这个主 - 细节方案,细节是一个树结构(一对多的自我关系),有n个级别(在SQLServer 2005上)
I need to copy a detail structure from one master to the another using a stored procedure, by passing the source master id and the target master id as parameters (the target is new, so it doesn't has details).
我需要使用存储过程将详细信息结构从一个主服务器复制到另一个主服务器,方法是将源主服务器标识和目标主服务器标识作为参数传递(目标是新的,因此它没有详细信息)。
I'm having troubles, and asking for your kind help in finding a way to keep track of parent id's and inserting the children without using cursors or nasty things like that...
我有麻烦,并寻求你的帮助,找到一种方法来跟踪父母的ID并插入孩子而不使用游标或令人讨厌的东西......
table model for the problem http://i38.tinypic.com/2vs3n7d.jpg
问题的表模型http://i38.tinypic.com/2vs3n7d.jpg
This is a sample model, of course, and what I'm trying to do is to copy the detail structure from one master to other. In fact, I'm creating a new master using an existing one as template.
当然,这是一个示例模型,我要做的是将详细结构从一个主服务器复制到另一个主服务器。实际上,我正在使用现有的一个作为模板创建一个新的主人。
2 个解决方案
#1
1
If I understand the problem, this might be what you want:
如果我理解了这个问题,这可能就是你想要的:
INSERT dbo.Master VALUES (@NewMaster_ID, @NewDescription)
INSERT dbo.Detail (parent_id, master_id, [name])
SELECT detail_ID, @NewMaster_ID, [name]
FROM dbo.Detail
WHERE master_id = @OldMaster_ID
UPDATE NewChild
SET parent_id = NewParent.detail_id
FROM dbo.Detail NewChild
JOIN dbo.Detail OldChild
ON NewChild.parent_id = OldChild.detail_id
JOIN dbo.Detail NewParent
ON NewParent.parent_id = OldChild.parent_ID
WHERE NewChild.master_id = @NewMaster_ID
AND NewParent.master_id = @NewMaster_ID
AND OldChild.master_id = @OldMaster_ID
The trick is to use the old detail_id as the new parent_id in the initial insert. Then join back to the old set of rows using this relationship, and update the new parent_id values.
诀窍是在初始插入中使用旧的detail_id作为新的parent_id。然后使用此关系连接回旧的行集,并更新新的parent_id值。
I assumed that detail_id is an IDENTITY value. If you assign them yourself, you'll need to provide details, but there's a similar solution.
我假设detail_id是IDENTITY值。如果您自己分配它们,则需要提供详细信息,但也有类似的解决方案。
#2
0
you'll have to provide create table and insert into statements for little sample data. and expected results based on this sample data.
您必须提供create table并插入语句中以获取少量样本数据。和基于此样本数据的预期结果。
#1
1
If I understand the problem, this might be what you want:
如果我理解了这个问题,这可能就是你想要的:
INSERT dbo.Master VALUES (@NewMaster_ID, @NewDescription)
INSERT dbo.Detail (parent_id, master_id, [name])
SELECT detail_ID, @NewMaster_ID, [name]
FROM dbo.Detail
WHERE master_id = @OldMaster_ID
UPDATE NewChild
SET parent_id = NewParent.detail_id
FROM dbo.Detail NewChild
JOIN dbo.Detail OldChild
ON NewChild.parent_id = OldChild.detail_id
JOIN dbo.Detail NewParent
ON NewParent.parent_id = OldChild.parent_ID
WHERE NewChild.master_id = @NewMaster_ID
AND NewParent.master_id = @NewMaster_ID
AND OldChild.master_id = @OldMaster_ID
The trick is to use the old detail_id as the new parent_id in the initial insert. Then join back to the old set of rows using this relationship, and update the new parent_id values.
诀窍是在初始插入中使用旧的detail_id作为新的parent_id。然后使用此关系连接回旧的行集,并更新新的parent_id值。
I assumed that detail_id is an IDENTITY value. If you assign them yourself, you'll need to provide details, but there's a similar solution.
我假设detail_id是IDENTITY值。如果您自己分配它们,则需要提供详细信息,但也有类似的解决方案。
#2
0
you'll have to provide create table and insert into statements for little sample data. and expected results based on this sample data.
您必须提供create table并插入语句中以获取少量样本数据。和基于此样本数据的预期结果。