Дублирование записей родительских, дочерних и внуков

У меня есть родительская таблица, которая представляет собой документ-сортировки, причем каждая запись в таблице содержит n записей детей в дочерней таблице. Каждая дочерняя запись может иметь n записей внуков. Эти записи находятся в опубликованном состоянии. Когда пользователь хочет изменить опубликованный документ, нам нужно клонировать родителя и всех его детей и внуков.

Структура таблицы выглядит следующим образом:

Родитель

CREATE TABLE [ql].[Quantlist] (
    [QuantlistId]   INT           IDENTITY (1, 1) NOT NULL,
    [StateId]       INT           NOT NULL,
    [Title]         VARCHAR (500) NOT NULL,
    CONSTRAINT [PK_Quantlist] PRIMARY KEY CLUSTERED ([QuantlistId] ASC),
    CONSTRAINT [FK_Quantlist_State] FOREIGN KEY ([StateId]) REFERENCES [ql].[State] ([StateId])
);

Ребенок

CREATE TABLE [ql].[QuantlistAttribute]
(
    [QuantlistAttributeId] INT IDENTITY (1, 1),
    [QuantlistId] INT NOT NULL,
    [Narrative] VARCHAR (500) NOT NULL,
    CONSTRAINT [PK_QuantlistAttribute] PRIMARY KEY ([QuantlistAttributeId]), 
    CONSTRAINT [FK_QuantlistAttribute_QuantlistId] FOREIGN KEY ([QuantlistId]) REFERENCES [ql].[Quantlist]([QuantlistId]),
)

Внук

CREATE TABLE [ql].[AttributeReference]
(
    [AttributeReferenceId] INT IDENTITY (1, 1),
    [QuantlistAttributeId] INT NOT NULL,
    [Reference] VARCHAR (250) NOT NULL,
    CONSTRAINT [PK_QuantlistReference] PRIMARY KEY ([AttributeReferenceId]), 
    CONSTRAINT [FK_QuantlistReference_QuantlistAttribute] FOREIGN KEY ([QuantlistAttributeId]) REFERENCES [ql].[QuantlistAttribute]([QuantlistAttributeId]),
)

В моей хранимой процедуре я передаю QuantlistId, я хочу клонировать как @QuantlistId. Поскольку таблица QuantlistAttribute имеет ForeignKey, я тоже могу легко клонировать ее.

INSERT INTO [ql].[Quantlist] (
    [StateId],
    [Title],
) SELECT 
    1,
    Title,
    FROM [ql].[Quantlist]
    WHERE QuantlistId = @QuantlistId

SET @ClonedId = SCOPE_IDENTITY()

INSERT INTO ql.QuantlistAttribute(
        QuantlistId
        ,Narrative)
    SELECT 
        @ClonedId,
        Narrative,
    FROM ql.QuantlistAttribute
    WHERE QuantlistId = @QuantlistId

Проблема сводится к AttributeReference. Если я клонировал 30 QuantlistAttribute записей, как мне клонировать записи в справочной таблице и сопоставлять их с новыми записями, которые я только что вставил в таблицу QuantlistAttribute?

    INSERT INTO ql.AttributeReference(
            QuantlistAttributeId,
            Reference,)
        SELECT 
            QuantlistAttributeId,
            Reference,
        FROM ql.QuantlistReference
        WHERE ??? I don't have a key to go off of for this.

Я думал, что смогу сделать это с помощью некоторых временных таблиц ссылок, содержащих старый идентификатор атрибута вместе с новым идентификатором атрибута. Я не знаю, как нужно вставлять старый идентификатор атрибута в временную таблицу вместе со своими новыми. Вставка существующих атрибутов по QuantlistId достаточно проста, но я не могу понять, как правильно связать правильный новый и старый идентификатор, так что таблица AttributeReference может быть клонирована правильно. Если бы я мог связать новый и старый идентификатор QuantlistAttribute, я мог бы присоединиться к этой временной таблице и выяснить, как восстановить связь недавно клонированных ссылок, с новыми клонированными атрибутами.

Любая помощь по этому поводу была бы потрясающей. Последние полтора дня я пытался выяснить это без везения:/

Прошу извинить некоторые несоответствия SQL. Я перезаписал sql real быстро, обрезая множество дополнительных столбцов, связанных таблиц и ограничений, которые не были нужны для этого вопроса.

Изменить

После небольшого копания, я обнаружил, что для этого может быть полезен OUTPUT. Есть ли способ использовать OUTPUT для отображения записей QuantlistAttributeId, которые я только что вставил, в QuantlistAttributeId они возникли из?

Ответ 1

Вы можете использовать OUTPUT, чтобы получить вставленные строки.

Вы можете вставить данные в QuantlistAttribute на основе порядка ORDER BY c.QuantlistAttributeId ASC
Имейте переменную temp table/table, которая содержит 3 столбца
- идентификационный столбец идентификатора
- new QuantlistAttributeId
- старый QuantlistAttributeId.
Используйте OUTPUT для вставки новых идентификационных значений QuantlistAttribute в переменную temp table/table. Новые идентификаторы создаются в том же порядке, что и c.QuantlistAttributeId
Используйте row_number(), упорядоченный по QuantlistAttributeId, чтобы соответствовать старым QuantlistAttributeId и новым QuantlistAttributeIds на основе row_number() и id переменной таблицы и обновить значения или старый QuantlistAttributeId в переменная таблицы
Используйте временную таблицу и join с помощью AttributeReference и вставьте записи за один раз.

Примечание: ORDER BY во время INSERT INTO SELECT и row_number() для получения совпадения старых QuantlistAttributeId требуется, потому что, глядя на ваш вопрос, похоже, нет другого логического ключа для сопоставления старых и новых записей.

Запрос для вышеуказанных шагов

DECLARE @ClonedId INT,@QuantlistId INT = 0

INSERT INTO [ql].[Quantlist] (
    [StateId],
    [Title]
) SELECT 
    1,
    Title
    FROM [ql].[Quantlist]
    WHERE QuantlistId = @QuantlistId

SET @ClonedId = SCOPE_IDENTITY()
--Define a table variable to store the new QuantlistAttributeID and use it to map with the Old QuantlistAttributeID
DECLARE @temp TABLE(id int identity(1,1), newAttrID INT,oldAttrID INT)

INSERT INTO ql.QuantlistAttribute(
        QuantlistId
        ,Narrative)
        --New QuantlistAttributeId are created in the same order as old QuantlistAttributeId  because of ORDER BY
        OUTPUT inserted.QuantlistAttributeId,NULL INTO @temp
    SELECT 
        @ClonedId,
        Narrative
    FROM ql.QuantlistAttribute c
    WHERE QuantlistId = @QuantlistId
    --This is required to keep new ids generated in the same order as old
    ORDER BY c.QuantlistAttributeId ASC

    ;WITH CTE AS
    (
        SELECT c.QuantlistAttributeId,
        --Use ROW_NUMBER to get matching id which is same as the one generated in @temp
        ROW_NUMBER()OVER(ORDER BY c.QuantlistAttributeId ASC) id
        FROM ql.QuantlistAttribute c
        WHERE QuantlistId = @QuantlistId
    )
    --Update the old value in @temp 
    UPDATE T
    SET oldAttrID = CTE.QuantlistAttributeId
    FROM @temp T
    INNER JOIN CTE ON T.id = CTE.id


INSERT INTO ql.AttributeReference(
            QuantlistAttributeId,
            Reference)
        SELECT 
            T.NewAttrID,
            Reference
        FROM ql.AttributeReference R
        --Use OldAttrID to join with ql.AttributeReference and insert NewAttrID
        INNER JOIN @temp T
        ON T.oldAttrID = R.QuantlistAttributeId

Надеюсь, что это поможет.