在 mssql 中复制多级相关表 [英] Copying multi level related tables in mssql

问题描述

我有 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.

这篇关于在 mssql 中复制多级相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！