gpt4 book ai didi

sql-server - TSQL 父 > 子 > 子子复制(无游标)

转载 作者:行者123 更新时间:2023-12-03 02:14:00 24 4
gpt4 key购买 nike

我正在创建一个 SQL 2008 R2 存储过程来复制一行及其所有子行。

这是一个 3 层设置,包含父级、子级和子级给定父级的 ID,我需要创建一个副本。

我已经使用 fast_forward cursor 解决了这个问题。

我知道我也可以通过行循环来完成此操作,但我不相信这会比这种游标方法更快。你有什么想法?

是否有更好的方法在不使用光标的情况下完成此任务?

编辑:我考虑的另一个选择是创建一个临时表来保存 TBLACStages 记录的旧/新 PKID。

TBLACStages 可能有 1 到 20 条对应行(并且 TBLACUpgrade 可能每个 TBLACStages 行有 3 行)

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

关于sql-server - TSQL 父 > 子 > 子子复制(无游标),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3613548/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com