在mssql中复制多级相关表

Question

我有4个相关的表，每个表与下一个表有1:N的关系，例如

One (OneID pk)
Two (TwoID pk, OneID fk)
Three (ThreeID pk, TwoID fk)
Four (FourID pk, ThreeID fk)

我需要实现的功能，当用户想要复制的记录在' 1 '和所有相关的记录在表二，三和四。

从前端完成此操作，以便用户可以基于现有记录创建新记录。最好的方法是什么?我有新插入的'OneID'和原来的'OneID'。

我想到的一种方法是为每个表创建一个"复制"存储过程，在每个表中都有一个游标，该游标为每行调用其子表Copy SP。

我想到的唯一另一种方法是有一个临时表，它有每个表的原始+新id的记录，但这看起来很乱，好像它可能会失控。

有什么建议吗?

Answer 1

如果您的pk是IDENTITY列，则可以使用本问题中描述的涉及MERGE的技术。

整个过程的脚本如下:

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.
*/

或者你可能不得不使用游标，这似乎是在SQL Server 2005和更早的版本中唯一的(理智的)方法。

Answer 2

过去我必须为大量数据执行此操作。我发现最好的方法是为每个表使用存储过程、临时表和GUID列。在我的例子中，我们有一个存储过程来完成所有表的复制，但是如果您愿意，也可以为每个表做一个。我们创建了一组临时表，这些表是我们要复制的所有表的精确副本，但是在不同的模式中并且没有键。在复制时，我们将所有记录的副本插入临时表中。然后，我们从临时表插入到dbo表中。其思想是首先插入没有fk的记录(这些是顶级项)。然后，在临时区中，对刚刚插入的顶级记录有引用的任何记录都将在临时表中更新其FK字段，然后将其插入dbo。您会发现，使用GUID列的原因是，为了更新外键，这是将复制的记录绑定回原始记录的唯一方法。如果您有4条记录都通过外键关系捆绑在一起，那么您将希望所有复制的记录都以相同的方式捆绑在一起。要做到这一点，唯一的方法是以某种方式跟踪原件和副本的id，并相应地更新它们。如果您要进行批量插入(就像我们一样)，那么GUID列是我们找到的唯一解决方案，但如果所有插入都是单个记录，那么您可能不需要在表中使用GUID列。下面是使用GUID列的快速示例:

-- copy data from dbo to temp schema
INSERT temp.One (field1, field2, guid, etc)
SELECT field1, field2, guid, etc
  FROM dbo.One
 WHERE OneID = @OneID
INSERT temp.Two (field1, field2, guid, etc)
SELECT field1, field2, guid, etc
  FROM dbo.Two t
 INNER JOIN temp.One o ON o.OneID = t.OneID
...
-- update GUIDs in temp area
UPDATE temp.One
   SET guid = NEWID()
UPDATE temp.Two
   SET guid = NEWID()
...
-- insert from temp to dbo
INSERT dbo.One (field1, field2, guid, etc)
SELECT field1, field2, guid, etc
  FROM temp.One
-- need to update FK here before inserting to dbo, join from temp to dbo on GUID
UPDATE temp.Two
   SET OneID = c.OneID
  FROM temp.Two t
 INNER JOIN temp.One o ON t.OneID = o.OneID
 INNER JOIN dbo.One c ON c.GUID = o.GUID
INSERT dbo.Two (field1, field2, guid, etc)
SELECT field1, field2, guid, etc
  FROM temp.Two
...

Answer 3

你基本上只需要一个表来映射你的旧/新值，如果你想保存副本的记录，一个物理表，如果你不想，一个临时表。

-- Create Tables
    CREATE TABLE #one (oneid UNIQUEIDENTIFIER)
    CREATE TABLE #two (twoid UNIQUEIDENTIFIER, oneid UNIQUEIDENTIFIER)
    CREATE TABLE #three (threeid UNIQUEIDENTIFIER, twoid UNIQUEIDENTIFIER)
    CREATE TABLE #four (fourid UNIQUEIDENTIFIER, threeid UNIQUEIDENTIFIER)
-- Insert test data
    DECLARE @guid UNIQUEIDENTIFIER
    SET @guid = newid()
    insert #one values (@guid)
    INSERT #two select NEWID(), oneid from #one
    INSERT #two select NEWID(), oneid from #one
    INSERT #two select NEWID(), oneid from #one
    INSERT #three SELECT NEWID(), twoid FROM #two WHERE oneid = @GUID
    INSERT #three SELECT NEWID(), twoid FROM #two WHERE oneid = @GUID
    INSERT #three SELECT NEWID(), twoid FROM #two WHERE oneid = @GUID
    INSERT #four SELECT NEWID(), threeid FROM #three WHERE twoid IN (SELECT twoid FROM #two WHERE oneid = @GUID)
    INSERT #four SELECT NEWID(), threeid FROM #three WHERE twoid IN (SELECT twoid FROM #two WHERE oneid = @GUID)
    INSERT #four SELECT NEWID(), threeid FROM #three WHERE twoid IN (SELECT twoid FROM #two WHERE oneid = @GUID)

-- Create temp tables
    CREATE TABLE #tempone (oneid UNIQUEIDENTIFIER, oldval UNIQUEIDENTIFIER)
    CREATE TABLE #temptwo (twoid UNIQUEIDENTIFIER, oneid UNIQUEIDENTIFIER, oldval UNIQUEIDENTIFIER)
    CREATE TABLE #tempthree (threeid UNIQUEIDENTIFIER, twoid UNIQUEIDENTIFIER, oldval UNIQUEIDENTIFIER)
    CREATE TABLE #tempfour (fourid UNIQUEIDENTIFIER, threeid UNIQUEIDENTIFIER, oldval UNIQUEIDENTIFIER)
    INSERT #tempone SELECT NEWID(), oneid FROM #one WHERE oneid = @guid
    INSERT #temptwo SELECT NEWID(), #tempone.oneid, #two.twoid FROM #two JOIN #tempone ON #two.oneid = #tempone.oldval
    INSERT #tempthree SELECT NEWID(), #temptwo.twoid, #three.threeid FROM #three JOIN #temptwo ON #three.twoid = #temptwo.oldval
    INSERT #tempfour SELECT NEWID(), #tempthree.threeid, #four.fourid FROM #four JOIN #tempthree ON #four.threeid = #tempthree.oldval
-- INSERT results
    INSERT #one SELECT t.oneid /*#one.column_list*/ FROM #tempone t JOIN #one oldT ON t.oldval = oldT.oneid
    INSERT #two SELECT t.twoid, t.oneid /*#two.column_list*/ FROM #temptwo t JOIN #two oldT ON t.oldval = oldT.twoid
    INSERT #three SELECT t.threeid, t.twoid /*#three.column_list*/ FROM #tempthree t JOIN #three oldT ON t.oldval = oldT.threeid    
    INSERT #four SELECT t.fourid, t.threeid /*#four.column_list*/ FROM #tempfour t JOIN #four oldT ON t.oldval = oldT.fourid    
-- View Results 
    SELECT one.oneid, two.twoid, three.threeid, four.fourid
    FROM #one one
    JOIN #two two ON one.oneid = two.oneid
    JOIN #three three on three.twoid = two.twoid
    JOIN #four four on four.threeid = three.threeid
    ORDER BY one.oneid, two.twoid, three.threeid, four.fourid