使用游标从表和子表复制行 [英] Duplicating Row from table and subtable using cursor

问题描述

我目前正在尝试复制表中的某些行，这些行也有一个子表（1对1的关系），它也会有重复的行。

如果我只有父行的一行（附件），这样可以正常工作，但由于可以有多行，我试图弄清楚如何使用游标插入一行，获取它的新身份密钥，然后申请子表中的那个键（attachmentContents）。

注意：我到目前为止，使用临时表来复制一行，并删除了RowVersion和AttachmentId字段，因为它们是身份字段和时间戳，当我转身并使用
重复行插回表格时，无需填写。

选择*进入＃TempTable3 FROM Rfi.Attachments，其中RequestId = @RequestId

ALTER TABLE＃TempTable3 DROP COLUMN AttachmentId

ALTER TABLE #TempTable3 DROP COLUMN RowVersion

UPDATE＃TempTable3 SET RequestId = @ NewRequestId

INSERT INTO Rfi.Attachments（

[RequestId]

      ，[姓名]

      ，[说明]

      ，[AttachmentTypeId]

      ，[ClassificationId]

      ，[ClassificationCaveat]

      ，[ClassificationCaveatId]）SELECT * from＃TempTable3

DROP TABLE＃TempTable3

-

- 一个附件C on
-

选择ac。*进入＃TempTable4 FROM Rfi.AttachmentContents ac

加入Rfi.Attachments a on（ac.AttachmentId = a.AttachmentId）

其中a.RequestId =@RequestId

ALTER TABLE #TempTable DROP COLUMN RequestId

INSERT INTO Rfi .AttachmentContents SELECT * from＃TempTable4

DROP TABLE＃TempTable4

---

Stockton

解决方案

您可以在一个位置执行所有行。使用OUTPUT子句捕获ID：

INSERT tbl（...）

   OUTPUT inserted.AttachmentID，inserted.col，... INTO @tbl（...）

  选择...    FROM 

为此，您需要能够从数据中的其他列映射attachmentID，因为您无法访问OUTPUT子句中的旧attachmentID。

...只要你使用INSERT。但是使用MERGE可能：

MERGE tbl

USING（SELECT ...）src ON 1 = 0

WHEN NOT匹配然后是
  插入（...）      VALUES（...）

OUTPUT src.AttachmentID，inserted.AttachmentID INTO @tbl（oldID，newID）

;

一旦你有了映射处理，子表应该是小菜一碟。

I currently am trying to duplicate some rows in a table, that also has a child table (1 to 1 relationship) that will also have rows duplicated.

If I only had 1 row of the parent (Attachments), this would work fine, but since there can be multiple rows, I'm trying to figure out how to use a cursor to insert a row, get it's new identity key, and then apply that key in the child table (attachmentContents).

note: what I have so far, uses a temp table to use to duplicate a row, and I remove the RowVersion and AttachmentId fields, because they are identity fields and timestamps, which need not be filled in when I turn around and insert back into the table with the duplicated row.

Select * Into #TempTable3 FROM Rfi.Attachments where RequestId = @RequestId
ALTER TABLE #TempTable3 DROP COLUMN AttachmentId
ALTER TABLE #TempTable3 DROP COLUMN RowVersion
UPDATE #TempTable3 SET RequestId=@NewRequestId
INSERT INTO Rfi.Attachments (
[RequestId]
      ,[Name]
      ,[Description]
      ,[AttachmentTypeId]
      ,[ClassificationId]
      ,[ClassificationCaveat]
      ,[ClassificationCaveatId]) SELECT * from #TempTable3
DROP TABLE #TempTable3
--
-- A t t a c h m e n t C o n t e n t s
--
Select ac.* Into #TempTable4 FROM Rfi.AttachmentContents ac
JOIN Rfi.Attachments a on (ac.AttachmentId = a.AttachmentId)
where a.RequestId=@RequestId
ALTER TABLE #TempTable DROP COLUMN RequestId
INSERT INTO Rfi.AttachmentContents SELECT * from #TempTable4
DROP TABLE #TempTable4

---

Stockton

解决方案

You can do all rows at one. Use the OUTPUT clause to capture the IDs:

INSERT tbl (...)
   OUTPUT inserted.AttachmentID, inserted.col, ... INTO @tbl(...)
   SELECT ...    FROM 
For this to work, you need to be able to map back the attachmentID from the other columns in the data, as there is no way you can access the old attachmentID in the OUTPUT clause.

...as long as you use INSERT. But with MERGE it is possible:

MERGE tbl
USING (SELECT ...) src ON 1 = 0
WHEN NOT MATCHED THEN
   INSERT (...)      VALUES(...)
OUTPUT src.AttachmentID, inserted.AttachmentID INTO @tbl(oldID, newID)
;

Once you have the mapping handling the sub-table should be a piece of cake.

这篇关于使用游标从表和子表复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！