问题描述
我有 4 个相关表,每个表与下一个表有 1:N 关系,例如
一个(OneID pk)两个(TwoID pk,OneID fk)三(ThreeID pk,TwoID fk)四(FourID pk,ThreeID fk)
当用户想要复制一"中的记录以及表二、三和四中的所有相关记录时,我需要实现功能.
从前端完成此操作,以便用户可以在现有记录的基础上创建新记录.做这个的最好方式是什么?我有新插入的OneID"和原始的OneID".
我想到的一种方法是为每个表创建一个复制"存储过程,在每个表中都有一个游标,为每一行调用它的子表复制 SP.
我想到的唯一另一种方法是创建一个临时表,其中记录了每个表的原始 ID + 新 ID,但这看起来很混乱,而且可能会失控.
有什么建议吗?
如果您的 PK 是 IDENTITY 列,您可以使用 MERGE 中描述的技术a href="https://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id" title="使用merge..output得到source.id 和 target.id 之间的映射">这个问题.
以下是整个过程的脚本编写方式:
DECLARE @OldID int, @NewID int;SET @OldID = some_value;声明 @TwoMapping 表 (OldID int, NewID int);声明 @ThreeMapping 表 (OldID int, NewID int);插入一个选择列从一哪里 OneID = @OldID;SET @NewID = SCOPE_IDENTITY();/*那个很简单:一行被复制,所以只需阅读 SCOPE_IDENTITY()插入后.实际的映射技术从这一点开始.*/合并两个 tgt使用 (选择@NewID 作为 OneID,其他栏目从两吨哪里 OneID = @OldID) 源文件开 0 = 1当不匹配时INSERT (columns) VALUES (src.columns)OUTPUT src.TwoID, INSERTED.TwoID INTO @TwoMapping (OldID, NewID);/*如您所见,MERGE 允许我们在OUTPUT 子句,除了伪表 INSERTED 和 DELETED,这是优于 INSERT 和方法核心的一大优势.*/
<预><代码>合并三个 tgt使用 (选择map.NewID AS TwoID,t.其他栏目从三吨INNER JOIN @TwoMapping 映射上 t.TwoID = map.OldID) 源文件开 0 = 1当不匹配时INSERT (columns) VALUES (src.columns)OUTPUT src.ThreeID, INSERTED.ThreeID INTO @ThreeMapping (OldID, NewID);/*现在我们有了一个映射表,我们可以很容易地用新的 FK 代替旧的具有简单连接的那些.在接下来的 MERGE 中再次重复相同的操作.*/合并四个 tgt使用 (选择map.NewID AS ThreeID,t.列从四吨INNER JOIN @ThreeMapping map ON t.ThreeID = map.OldID) 源文件开 0 = 1当不匹配时INSERT (columns) VALUES (src.columns);/*Four 表是依赖链中的最后一个,所以最后一个 MERGE没有 OUTPUT 子句.但是如果有一张五人桌,我们会像上面那样继续.*/
或者,您可能不得不使用游标,这似乎是在 SQL Server 2005 及更早版本中执行此操作的唯一(合理)方法.
I have 4 related tables, each has a 1:N relationship with the next table, e.g.
One (OneID pk) Two (TwoID pk, OneID fk) Three (ThreeID pk, TwoID fk) Four (FourID pk, ThreeID fk)
I need to implement functionality for when the user wants to copy a record in 'One' and all related records in tables Two, Three and Four.
From the front end this is done so that the user can base a new record on an existing one. What is the best way to do this? I have the newly inserted 'OneID' and the Original 'OneID'.
One way that I've thought of doing this is to have a 'Copy' stored procedure for each table, in each of them have a cursor that calls it's child tables Copy SP once for each row.
The only other way I've thought of doing it was to have a temp table that has a record of the original + new IDs for each table but this seemed messy and like it could get out of hand.
Any suggestions?
If your PKs are IDENTITY columns, you could use a technique involving MERGE that is described in this question.
Here's how the entire process might be scripted:
DECLARE @OldID int, @NewID int; SET @OldID = some_value; DECLARE @TwoMapping TABLE (OldID int, NewID int); DECLARE @ThreeMapping TABLE (OldID int, NewID int); INSERT INTO One SELECT columns FROM One WHERE OneID = @OldID; SET @NewID = SCOPE_IDENTITY(); /* That one was simple: one row is copied, so just reading SCOPE_IDENTITY() after the INSERT. The actual mapping technique starts at this point. */ MERGE Two tgt USING ( SELECT @NewID AS OneID, other columns FROM Two t WHERE OneID = @OldID ) src ON 0 = 1 WHEN NOT MATCHED THEN INSERT (columns) VALUES (src.columns) OUTPUT src.TwoID, INSERTED.TwoID INTO @TwoMapping (OldID, NewID); /* As you can see, MERGE allows us to reference the source table in the OUTPUT clause, in addition to the pseudo-tables INSERTED and DELETED, and that is a great advantage over INSERT and the core of the method. */
MERGE Three tgt USING ( SELECT map.NewID AS TwoID, t.other columns FROM Three t INNER JOIN @TwoMapping map ON t.TwoID = map.OldID ) src ON 0 = 1 WHEN NOT MATCHED THEN INSERT (columns) VALUES (src.columns) OUTPUT src.ThreeID, INSERTED.ThreeID INTO @ThreeMapping (OldID, NewID); /* Now that we've got a mapping table, we can easily substitute new FKs for the old ones with a simple join. The same is repeated once again in the following MERGE. */ MERGE Four tgt USING ( SELECT map.NewID AS ThreeID, t.columns FROM Four t INNER JOIN @ThreeMapping map ON t.ThreeID = map.OldID ) src ON 0 = 1 WHEN NOT MATCHED THEN INSERT (columns) VALUES (src.columns); /* The Four table is the last one in the chain of dependencies, so the last MERGE has no OUTPUT clause. But if there were a Five table, we would go on like above. */
Alternatively you'd probably have to use cursors, which seems to be the only (sane) way of doing this in SQL Server 2005 and earlier versions.