My<cod/>.net
Вставляйте обновление хранимой процедуре на сервере SQL
Я написал сохраненный proc, который будет выполнять обновление, если запись существует, в противном случае он будет выполнять вставку. Это выглядит примерно так:
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
Моя логика написания его таким образом заключается в том, что обновление будет выполнять неявный выбор с помощью предложения where, и если это возвращает 0, то вставка будет иметь место.
Альтернативой этому способу было бы сделать выбор, а затем на основе количества возвращенных строк либо выполнить обновление, либо вставить. Это я посчитал неэффективным, потому что если вы собираетесь сделать обновление, это вызовет 2 выбора (первый явный вызов select и второй неявный в where обновления). Если бы proc должен был сделать вставку, то не было бы никакой разницы в эффективности.
Разве моя логика здесь верна? Это то, как вы бы объединили вставку и обновление в сохраненный proc?
Ваше предположение верно, это оптимальный способ сделать это, и он называется upsert/merge .
Важность UPSERT-от sqlservercentral.com :
Для каждого обновления в случае упомянутом выше мы удаляем один дополнительное чтение из таблицы, если мы используйте UPSERT вместо EXISTS. К несчастью для вставки, оба UPSERT и если существуют методы, то используйте такое же количество считываний на столе. Поэтому проверка на существование должно быть сделано только тогда, когда есть очень веская причина для оправдания дополнительный I/O. оптимизированный способ делай вещи так, чтобы убедиться, что ты имейте немного читает как можно дальше DB.
Наилучшая стратегия-это попытка обновление. Если ни на какие строки не влияет обновить, а затем вставить. В большинстве обстоятельства, ряд уже будет существует и только один вход / выход будет требуемый.
Редактировать : Пожалуйста, ознакомьтесь с этим ответом и связанным с ним сообщением в блоге, чтобы узнать о проблемах с этим шаблоном и о том, как сделать его работу безопасной.
Пожалуйста, прочитайте сообщение в моем блоге для хорошего, безопасного шаблона, который вы можете использовать. Есть много соображений, и принятый ответ на этот вопрос далеко не безопасен.
Для быстрого ответа попробуйте воспользоваться следующей схемой. Он будет прекрасно работать на SQL 2000 и выше. SQL 2005 дает вам обработку ошибок, которая открывает другие опции, а SQL 2008 дает вам команду MERGE.
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
При использовании с SQL Server 2000/2005 исходный код должен быть заключен в транзакцию, чтобы убедиться, что данные остаются согласованными в параллельном сценарии.
BEGIN TRANSACTION Upsert
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
COMMIT TRANSACTION Upsert
Это потребует дополнительных затрат на производительность, но обеспечит целостность данных.
Добавить, как уже было предложено, MERGE следует использовать там, где это возможно.
Вам нужно не только запустить его в транзакции, но и обеспечить высокий уровень изоляции. Я думаю, что уровень изоляции по умолчанию прочитан, и этот код нуждается в сериализации.
SET transaction isolation level SERIALIZABLE
BEGIN TRANSACTION Upsert
UPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
begin
INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)
end
COMMIT TRANSACTION Upsert
Возможно, добавление также проверки @@error и отката может быть хорошей идеей.
Большой поклонник UPSERT, действительно сокращает код для управления. Вот еще один способ, которым я это делаю: один из входных параметров-ID, если ID - это NULL или 0, вы знаете, что это INSERT, иначе это обновление. Предполагается, что приложение знает, есть ли ID, поэтому не будет работать во всех ситуациях, но сократит выполнение вдвое, если вы это сделаете.
Ваша логика кажется здравой, но вы можете рассмотреть возможность добавления некоторого кода, чтобы предотвратить вставку, если вы передали определенный первичный ключ.
В противном случае, если вы всегда выполняете вставку, если обновление не повлияло на какие-либо записи, что происходит, когда кто-то удаляет запись перед запуском "UPSERT"? Теперь запись, которую вы пытались обновить, не существует, поэтому вместо нее будет создана запись. Вероятно, это не то поведение, которое вы искали.
Модифицированный пост Димы Маленко:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION UPSERT
UPDATE MYTABLE
SET COL1 = @col1,
COL2 = @col2
WHERE ID = @ID
IF @@rowcount = 0
BEGIN
INSERT INTO MYTABLE
(ID,
COL1,
COL2)
VALUES (@ID,
@col1,
@col2)
END
IF @@Error > 0
BEGIN
INSERT INTO MYERRORTABLE
(ID,
COL1,
COL2)
VALUES (@ID,
@col1,
@col2)
END
COMMIT TRANSACTION UPSERT
Вы можете поймать ошибку и отправить запись в неудачную таблицу вставки.
Мне нужно было сделать это, потому что мы берем любые данные, отправляемые через WSDL, и по возможности исправляем их внутри.