TSQL父>子>没有游标的子子复制 [英] TSQL Parent > Child > Sub-Child duplication without cursor
问题描述
我正在创建一个SQL 2008 R2存储过程来复制一行及其所有子级.
I am creating a SQL 2008 R2 stored procedure to duplicate a row and all it's children.
这是一个具有父级,子级和子级子的三层设置 给定父级的ID,我需要创建一个副本.
It's a 3-tiered setup with a Parent, Child and Sub-Child Given the ID of the parent I need to create a duplicate.
我已经使用fast_forward
cursor
解决了它.
I have solved it using a fast_forward
cursor
.
我知道我也可以通过循环遍历行来做到这一点,但我不认为这会比此游标方法快.你有什么想法?
I know I can also do it with a while loop through rows but I do not believe that will be faster than this cursor method. What are your thoughts?
有没有更好的方法可以在不使用光标的情况下完成此任务?
我考虑的另一种选择是创建一个临时表,其中包含TBLACStages记录的旧/新PKID.
Another option I considered was creating a temp table holding the old / new PKID's of the TBLACStages records.
TBLACStages可能有1到20条对应的行(并且TBLACUpgrade可能每TBLACStages行有3行)
TBLACStages may have anywhere from 1 to 20 corresponding rows (and TBLACUpgrade will likely have 3 rows per TBLACStages row)
CREATE PROCEDURE [dbo].[spDuplicateACUnit]
@pACUnitID bigint = 0
AS BEGIN
SET NOCOUNT ON;
DECLARE @NewACUnitID bigint = 0
INSERT INTO TBLACUnits ([col1] ,[col2] ,[...] ,[coln]) SELECT [col1] ,[col2] ,[...] ,[coln] FROM TBLACUnits WHERE ACUnitID = @pACUnitID
SELECT @NewACUnitID = SCOPE_IDENTITY()
DECLARE @ACStageID bigint = 0
DECLARE @NewACStageID bigint = 0
DECLARE @ACUnitCursor CURSOR
SET @ACUnitCursor = CURSOR LOCAL FAST_FORWARD FOR SELECT ACStageID FROM TBLACStages WHERE TBLACStages.ACUnitID = @pACUnitID
OPEN @ACUnitCursor
FETCH NEXT FROM @ACUnitCursor INTO @ACStageID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TBLACStages ([ACUnitID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACUnitID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACStages WHERE TBLACStages.ACStageID = @ACStageID
SELECT @NewACStageID = SCOPE_IDENTITY()
INSERT INTO TBLACUpgrade ([ACStageID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACStageID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACUpgrade WHERE TBLACUpgrade.[ACStageID] = @ACStageID
FETCH NEXT FROM @ACUnitCursor INTO @ACStageID
END
CLOSE @ACUnitCursor DEALLOCATE @ACUnitCursor
END
GO
推荐答案
这应该给您一个想法:
CREATE TABLE t_parent (id INT NOT NULL PRIMARY KEY IDENTITY, value VARCHAR(100))
CREATE TABLE t_child (id INT NOT NULL PRIMARY KEY IDENTITY, parent INT NOT NULL, value VARCHAR(100))
CREATE TABLE t_grandchild (id INT NOT NULL PRIMARY KEY IDENTITY, child INT NOT NULL, value VARCHAR(100))
INSERT
INTO t_parent (value)
VALUES ('Parent 1')
INSERT
INTO t_parent (value)
VALUES ('Parent 2')
INSERT
INTO t_child (parent, value)
VALUES (1, 'Child 2')
INSERT
INTO t_child (parent, value)
VALUES (2, 'Child 2')
INSERT
INTO t_grandchild (child, value)
VALUES (1, 'Grandchild 1')
INSERT
INTO t_grandchild (child, value)
VALUES (1, 'Grandchild 2')
INSERT
INTO t_grandchild (child, value)
VALUES (2, 'Grandchild 3')
DECLARE @parent TABLE (oid INT, nid INT)
DECLARE @child TABLE (oid INT, nid INT)
MERGE
INTO t_parent
USING (
SELECT id, value
FROM t_parent
) p
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (value)
VALUES (value)
OUTPUT p.id, INSERTED.id
INTO @parent;
SELECT *
FROM @parent
MERGE
INTO t_child
USING (
SELECT c.id, p.nid, c.value
FROM @parent p
JOIN t_child c
ON c.parent = p.oid
) c
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (parent, value)
VALUES (nid, value)
OUTPUT c.id, INSERTED.id
INTO @child;
SELECT *
FROM @child;
INSERT
INTO t_grandchild (child, value)
SELECT c.nid, gc.value
FROM @child c
JOIN t_grandchild gc
ON gc.child = c.oid
SELECT *
FROM t_grandchild
这篇关于TSQL父>子>没有游标的子子复制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!