Stored Proc на SQL Server: Обновление или вставка

Быстрый ответ

В SQL Server для одновременного обновления существующих строк и вставки новых применяется оператор MERGE. За счёт выполнения действий upsert в рамках одного запроса, MERGE становится оптимальным решением для условного выполнения операций, в зависимости от наличия записи в базе данных:

MERGE TargetTable AS T
USING SourceTable AS S
ON T.Key = S.Key
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value
WHEN NOT MATCHED THEN
    INSERT (Key, Value) VALUES (S.Key, S.Value);

Изучение Upsert: основные принципы

Отдаем предпочтение обновлениям: повышаем производительность

Стратегия, основанная на приоритете обновления, помогает минимизировать количество обращений к таблице. Если запись предположительно имеется, то сначала выполняем обновление, а затем, с использованием @@rowcount, производим проверку на необходимость вставки:

UPDATE TargetTable
SET Value = S.Value
FROM SourceTable AS S
WHERE TargetTable.Key = S.Key;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO TargetTable (Key, Value)
    SELECT Key, Value
    FROM SourceTable
    WHERE NOT EXISTS (
        SELECT 1 FROM TargetTable WHERE Key = SourceTable.Key
    );
END;

Работаем с параллельностью

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

BEGIN TRANSACTION;

MERGE TargetTable WITH (HOLDLOCK) AS T
...

COMMIT TRANSACTION;

Рекомендуется также использовать уровень изоляции SERIALIZABLE для управления параллельными доступами.

Правильная обработка ошибок

Реализуйте стратегии обработки ошибок, чтобы при возникновении сбоя откатить транзакции и сохранить целостность данных:

BEGIN TRY
    BEGIN TRANSACTION;
    -- Логика upsert
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Обработка ошибки
END CATCH;

Точный подход к проверкам существования

Перед реализацией проверки на существование с помощью SELECT оцените влияние на производительность и соответствие логики вашим требованиям.

Визуализация

Определите хранимую процедуру как набор инструментов для работы с базой данных, состоящий из двух основных элемнтов:

Набор инструментов (🧰): [Инструмент Вставки (🔨), Инструмент Обновления (🔧)]

Когда появляется новый объект для вставки (🔩):

Если объект соответствует существующей записи: 
    Используем Инструмент Обновления (🔧)
        🔧 + 🔩 -> Запись обновлена! (🕳️)
Иначе:
    Используем Инструмент Вставки (🔨) 
        🔨 + 🔩 -> Создана новая запись! (🕳️🆕)

Процедура выбирает инструмент на основе того, имеется ли для объекта соответствующая запись:

🧰🔍🔩 -> (🔧 или 🔨?) -> 🕳️ (🔄 или 🆕)

В результате выполнения процедуры:

Входные данные: [🔩, 🔩🆕, 🔩🆕]
С использованием 🧰:
    Для существующего '🔩' применено: 🔧🔄🕳️
    Для '🔩🆕' использовано: 🔨🆕🕳️
    Для еще одного '🔩🆕' применено: 🔨🆕🕳️

Выбирая подходящий инструмент, вы можете максимально эффективно проводить операции вставки и обновления, обновляя базу данных без лишних действий.

Продвинутые подходы для мастеров Upsert

Улучшение производительности для Upsert

Любой подход upsert влияет на производительность. Мощный оператор MERGE, если его использовать неправильно, может оказаться ресурсоемким. Отслеживайте планы запросов и статистику, чтобы убедиться, что оператор действительно подходит для задачи.

Ожидайте новых функций SQL Server

Следите за обновлениями SQL Server, включающими улучшения выполнения операций upsert. Новые версии могут предлагать усовершенствования синтаксиса и производительности.

Преимущества знаний сообщества

Обращайтесь к блогам, форумам и другим источникам, чтобы узнать о реальной практике применения upsert в продвинутом производственном контексте.

Полезные материалы

1. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация Microsoft по использованию оператора MERGE.
2. Using hash values in SSIS to determine when to insert or update rows — руководство о применении хеш-значений в SSIS для определения момента вставки или обновления данных.
3. Tutorial: Handle Concurrency with EF in an ASP.NET MVC 5 app | Microsoft Learn — учебное пособие по управлению параллельностью в .NET приложениях с использованием Entity Framework.