В MySQL есть эта невероятно полезная, но проприетарная SQL-команда REPLACE INTO
.
Можно ли это легко эмулировать в SQL Server 2005?
Запуск новой транзакции, выполнение Select()
, а затем UPDATE
или INSERT
и COMMIT
всегда немного неудобно, особенно при выполнении этого в приложении и, следовательно, всегда с сохранением двух версий оператора.
Интересно, есть ли простой и универсальный способ реализовать такую функцию в SQL Server 2005?
Функциональность, которую вы ищете, традиционно называется UPSERT. По крайней мере, знание того, как это называется, может помочь вам найти то, что вы ищете.
Я не думаю, что у SQL Server 2005 есть какие-то отличные способы сделать это. 2008 вводит оператор MERGE, который можно использовать для этого, как показано в: http://www.databasejournal.com/features/mssql/article.php/3739131 или http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx
Слияние было доступно в бета-версии 2005 года, но они удалили его в финальной версии.
То, что делает upsert / merge, похоже на ...
IF EXISTS (SELECT * FROM [Table] WHERE Id = X)
UPDATE [Table] SET...
ELSE
INSERT INTO [Table]
Так что, надеюсь, комбинация этих статей и этого псевдокода может сдвинуть дело с мертвой точки.
Это то, что меня раздражает в MSSQL (разглагольствовать в моем блоге). Я бы хотел, чтобы MSSQL поддерживал upsert
.
Код @Dillie-O подходит для более старых версий SQL (+1 голос), но в основном это две операции ввода-вывода (exists
, а затем update
или insert
).
Есть немного лучший способ на эта почта, в основном:
--try an update
update tablename
set field1 = 'new value',
field2 = 'different value',
...
where idfield = 7
--insert if failed
if @@rowcount = 0 and @@error = 0
insert into tablename
( idfield, field1, field2, ... )
values ( 7, 'value one', 'another value', ... )
Это сокращает его до одной операции ввода-вывода, если это обновление, или двух, если это вставка.
MS Sql2008 представляет merge
из стандарта SQL: 2003:
merge tablename as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
Теперь это действительно всего одна операция ввода-вывода, но ужасный код :-(
@Michael. Если вы собираетесь использовать это решение, вам лучше иметь уникальный индекс для этой таблицы и обрабатывать повторяющиеся ключевые ошибки.
@Keith Ваш оператор слияния не работает. MERGE
не поддерживает предложение WHERE
, вы должны переписать его, используя USING
и ON
. Кроме того, если вы не добавите WITH (HOLDLOCK)
, может произойти гонка и одновременные INSERT
, при этом один из них выйдет из строя из-за конфликта ключей.
Да, как указано здесь: weblogs.sqlteam.com/dang/archive/2009/01/31/… MERGE не является атомарным. Он снимает неявную блокировку обновления, но снимает ее перед выполнением вставки, что вызывает состояние гонки, которое может привести к нарушениям первичного ключа. Вы должны использовать явный HOLDLOCK в дополнение к неявному UPDLOCK, чтобы операция была атомарной. В его нынешнем виде он не является атомарным, несмотря на то, что выглядит как единое утверждение.
Синтаксис MERGE неверен, и он исправлен в более позднем ответе того же автора: stackoverflow.com/a/243670/24472
Вышеупомянутая вставка с последующим обновлением все еще может не работать. С уникальным ключом вы можете получить повторяющиеся ключевые исключения во вставке. Я рекомендую добавить в ваш оператор обновления: UPDATE table WITH (SERIALIZABLE) SET ...
Я написал Сообщение блога об этой проблеме.
Суть в том, что если вам нужны дешевые обновления и вы хотите обезопасить себя от одновременного использования, попробуйте:
update t
set hitCount = hitCount + 1
where pk = @id
if @@rowcount < 1
begin
begin tran
update t with (serializable)
set hitCount = hitCount + 1
where pk = @id
if @@rowcount = 0
begin
insert t (pk, hitCount)
values (@id,1)
end
commit tran
end
Таким образом, у вас есть 1 операция для обновления и максимум 3 операции для вставки. Так что, если вы обычно обновляетесь, это безопасный дешевый вариант.
Я также был бы очень осторожен, чтобы не использовать что-либо небезопасное для одновременного использования. Очень легко получить нарушения первичного ключа или повторяющиеся строки в производственной среде.
Большое спасибо! Сохраняет Select и часто даже не требует теракции в ситуациях, когда я могу быть уверен, что между обновлением и «моей» вставкой нет другой вставки для этого ключа.