Использование MERGE для удаления данных или вставки

Из пользовательского интерфейса я передаю данные в хранимую процедуру. Тип этого параметра — определяемое пользователем поле таблицы со следующей структурой

Personkey int
ComponentKey varchar

Эти данные должны попасть в таблицу, а данные, которые существуют в таблице, но не в таблице данных, должны быть удалены.

Пример данных таблицы

PersonKey     ComponentKey
123           A1
456           B9

И моя таблица данных имеет 2 строки, одну совпадающую строку и одну новую строку

Пример данных

PersonKey    ComponentKey
123          A1
786          Z6

В результате строка 456/B9 должна быть удалена, со строкой 123/A1 ничего не должно произойти, а строка 786/Z6 должна быть вставлена.

Я считаю, что могу использовать оператор MERGE, но я не уверен, как его сформировать.

Я понимаю, что WHEN NOT MATCHED я должен сделать вставку, но при чем здесь часть удаления?

MERGE Components
USING @passedInData
ON PersonKey = DatatblPersonKey AND ComponentKey = DatatblComponentKey

WHEN MATCHED THEN
-- DO nothing...

WHEN NOT MATCHED
INSERT (PersonKey, ComponentKey) VALUES (DatatblPersonKey, DatatblComponentey);

Изменить: просто для ясности: таблица данных может содержать много строк для одного и того же ключа человека, но ключ компонента будет другим.

Пример данных

PersonKey    ComponentKey
123          Z6
123          C5

Пример данных таблицы

PersonKey     ComponentKey
123           A1
456           B9

Результат после вставки вышеуказанной таблицы данных должен быть

PersonKey    ComponentKey
123          Z6
123          C5
456          B9

Обратите внимание, что 123/A1 был удален, а 456/B9 все еще находится в таблице.


datatable sql-server sql-merge
person andrewb    schedule 31.08.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Значение по умолчанию «КОГДА НЕ СООТВЕТСТВУЕТ» предполагает, что на самом деле вы имеете в виду «КОГДА НЕ СООТВЕТСТВУЕТ ЦЕЛИ». Вы можете сделать еще один оператор для «КОГДА НЕ СООТВЕТСТВУЕТ ИСТОЧНИКУ» с помощью простой команды «УДАЛИТЬ».

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

изменить... демонстрируя, как подключить то, что я говорю:

DECLARE @databaseTable TABLE (PersonKey INT, ComponentKey VARCHAR(10));
INSERT INTO @databaseTable
VALUES
    (123, 'A1'),
    (456, 'B9');
DECLARE @appDataset TABLE (PersonKey INT, ComponentKey VARCHAR(10));
INSERT INTO @appDataset
VALUES
    (123, 'Z6'),
    (123, 'C5');

WITH cteTarget AS
    (
    SELECT  dt.PersonKey
        ,   dt.ComponentKey
    FROM @databaseTable AS dt
    JOIN (SELECT DISTINCT PersonKey FROM @appDataset) AS pk
        ON pk.PersonKey = dt.PersonKey
    )
MERGE cteTarget AS tgt
USING @appDataset AS src
    ON src.PersonKey = tgt.PersonKey
    AND src.ComponentKey = tgt.ComponentKey
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
WHEN NOT MATCHED BY TARGET THEN
    INSERT
        (PersonKey
        ,ComponentKey)
    VALUES
        (src.PersonKey
        ,src.ComponentKey);

SELECT * FROM @databaseTable;
person btberry    schedule 31.08.2016
comment
Я обновил свой вопрос, так как этот подход не сработал. - person andrewb; 01.09.2016
comment
Возможно, ваше исполнение не сработало, но подход сработал. Я отредактировал свой ответ, включив в него конструкции данных и закодированное решение для вашей проблемы. Опять же, имейте в виду, что вы должны позаботиться о том, чтобы определить, что именно является вашей целью. - person btberry; 01.09.2016
comment
Стоит упомянуть, что MERGE не может сопоставлять столбцы, где значение равно NULL, поэтому, если src.ComponentKey и tgt.ComponentKey оба имеют значение NULL, хотя на самом деле это совпадение, SQL не будет рассматривать его как одно. Чтобы избежать этого, убедитесь, что вы ISNULL каждый столбец в вашем операторе USING. - person Geoff Griswald; 29.01.2021