Вставить обновление хранимой процедуры на SQL Server

Question

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

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 и второй неявный в месте, где находится обновление). Если бы proc сделал вставку, то не было бы никакой разницы в эффективности.

Моя логика здесь звучит? Это как вы могли бы объединить вставку и обновление в сохраненный процесс?

Answer 1

Ваше предположение верно, это оптимальный способ сделать это и называется upsert / merge.

Важность UPSERT - от sqlservercentral.com:

Для каждого обновления в вышеупомянутом случае мы удаляем одно дополнительное чтение из таблицы, если мы используем UPSERT вместо EXISTS. К сожалению, для вставки оба метода UPSERT и IF EXISTS используют одинаковое количество операций чтения в таблице. Поэтому проверка на существование должна выполняться только в том случае, если есть очень веская причина для обоснования дополнительного ввода / вывода. Оптимизированный способ сделать все, чтобы убедиться, что у вас мало чтений на БД.

Лучшая стратегия - попытаться обновить. Если обновление не затронуло ни одной строки, вставьте. В большинстве случаев строка уже существует, и потребуется только один ввод / вывод.

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

Answer 2

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

Для быстрого ответа попробуйте следующую схему. Это будет хорошо работать на 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

Answer 3

При использовании с 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 следует использовать там, где это возможно.

Answer 4

Кстати, MERGE - это одна из новых функций в SQL Server 2008.

Answer 5

Вам нужно не только запустить его в транзакции, но и высокий уровень изоляции. Фактически, уровень изоляции по умолчанию - Read Commited, и этот код должен быть Serializable.

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

Может быть, было бы неплохо добавить @@ проверку ошибок и откат.

Answer 6

Если вы не делаете слияние в SQL 2008, вы должны изменить его на:

если @@rowcount = 0 и @@error=0

в противном случае, если обновление завершится неудачей по какой-либо причине, оно попытается выполнить вставку позже, поскольку число строк в невыполненном операторе равно 0

Answer 7

Большой поклонник UPSERT, действительно сокращает код для управления. Вот еще один способ, которым я делаю это: один из входных параметров - это ID, если ID равен NULL или 0, вы знаете, что это INSERT, в противном случае это обновление. Предполагается, что приложение знает, есть ли идентификатор, поэтому не будет работать во всех ситуациях, но при этом сократит количество выполнений пополам.

Answer 8

Модифицированный пост Димы Маленко:

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, и, если возможно, исправляем их внутренне.

Answer 9

Ваша логика кажется разумной, но вы можете рассмотреть возможность добавления некоторого кода, чтобы предотвратить вставку, если вы передали определенный первичный ключ.

В противном случае, если вы всегда выполняете вставку, если обновление не затрагивает какие-либо записи, что произойдет, если кто-то удалит запись перед запуском "UPSERT"? Теперь запись, которую вы пытались обновить, не существует, поэтому вместо нее будет создана запись. Вероятно, это не то поведение, которое вы искали.