問題描述
我有 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 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 中復制多級相關表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！