Сегодня хочу немного рассказать о том, как реализовать в MSSQL возможность, встроенную в MySQL, позволяющую обновить запись при вставке, если она уже существует. Одним словом, так называемый UPSERT.
В MySQL это выглядит так:
INSERT INTO(id, text) VALUES(12, 'Current text')
ON DUPLICATE KEY UPDATE text='New text';
В MSSQL такого нет, хотя это и странно. Пробежавшись по ссылкам стало понятно, что вариантов на самом деле несколько, но все они не ахти какие удобные, и уж точно не короткие и изящные. Перечислим же их, возможно вам понравится хоть что-то.
Первый вариант - это просто проверять существование записи перед добавлением. Гром и молния, кэп, как я сам не догадался! Да и не нужен бы мне тогда был upsert, правда? Это совсем не то, что я ищу, отпадает.
Второй вариант, который настойчиво предлагают советчики, это использовать MERGE. Его смысл - перенос данных из одной таблицы (запроса) в другую с проверкой совпадений значений некоторых полей. Что-ж, вроде годная штука, давайте попробуем. Нижеописанную задачу во многих случаях можно решить группировкой исходных данных, но не во всех. Нам нужно при совпадении значений полей увеличивать значение поля в таблице, куда переносятся данные, вместо копирования новой строки.
-- Создадим переменную типа таблицы с составным уникальным индексом
DECLARE @temp TABLE(word NVARCHAR(512), term_id INT, word_count SMALLINT, UNIQUE(word, term_id))
MERGE @temp as TMP
-- Источник данных, им может быть и таблица, и выборка
USING (SELECT word, term_id FROM table1) as TWT
-- Условия совпадения, при которых будем делать Update
ON TMP.word = TWT.word AND TMP.term_id = TWT.term_id
WHEN MATCHED THEN
-- Нашли совпадение, изменим значение поля счетчика
UPDATE SET TMP.word_count += 1
WHEN NOT MATCHED THEN
-- Совпадения не найдено, добавляем
INSERT (word, term_id word_count) VALUES (TWT.word, TWT.term_id, 1)
;
Сюрприз! При добавлении строк проверка осуществляется только в начале, а строки, которые будут добавлены потом на наличие совпадений не проверяются. Это легко можно проверить, добавив вручную в таблицу @temp пару строк, которые вскоре будут туда добавляться запросом. Так что для нашего случая это не годится.
Наконец третий, рабочий, но медленный вариант. Мы будем использовать конструкцию BEGIN TRY. Работаем с той-же таблицей @temp, что в предыдущем примере. Мы будем проходит курсором по выборке из исходной таблице и пробовать добавлять в @temp, и обновлять данные при ошибке уникального ключа.
-- Переменные, куда будем складывать данные из курсора
DECLARE @word NVARCHAR(512)
DECLARE @term_id INT
-- Объявляем и открываем курсор с данными
DECLARE words SCROLL CURSOR FOR SELECT word, term_id FROM table1
OPEN words
-- Проходим по курсору
FETCH NEXT FROM words INTO @word, @term_id WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Пробуем вставить строку
INSERT INTO @temp VALUES(@word, @term_id, 1)
END TRY
BEGIN CATCH
-- При ошибке делаем update
UPDATE @temp SET word_count = word_count + 1
WHERE word = @word AND term_id = @term_id
END CATCH
-- Берем данные из следующей строки
FETCH NEXT FROM words INTO @word, @term_id
END
-- Закрываем и освобождаем курсор
CLOSE words
DEALLOCATE words
Вот такие я подобрал варианты. Надеюсь, кому-нибудь мои изыскания помогут.
