Copying multi level related tables in mssql

问题:

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?

回答1:

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.

回答2:

I've had to do this in the past for a large set of data. I found the best way to be using a stored procedure, temp tables, and GUID column for each table. In my case we had one stored procedure that did all the copying for all tables involved, but you could do one for each table if you wish. We made a set of temp tables that were an exact copy of all the tables we would be copying from, but in a different schema and with no keys. When copying, we inserted a copy of all the records into the temp tables. Then, we inserted from the temp tables back into the dbo tables. The idea is to first insert records that have no FKs (these are top-level items). Then, in the temp area any records that have a reference to the top-level record that was just inserted have their FK field updated in the temp table and are then inserted into dbo. The reason for the GUID column, you will find, is that was the only way to tie the copied record back to the original for the purpose of updating the foriegn keys. If you have 4 records all tied together by foriegn key relationships, then you would want all of your copied records to be tied together in the same way. The only way to do that is to keep track, in some way, of the IDs of the originals and the IDs of the copies and update them accordingly. If you will be doing batch inserts (as we were) then a GUID column was the only solution we found, but if all inserts are single records, then you may not need a GUID column in the tables. Here's a quick idea of how it goes using a GUID column:

-- 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

...

回答3:

You basically just need a table to map your old/new values, a physical table if you want to keep a record of the copies, a temp table if you don't.

-- 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