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

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

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

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

17.08.2008 06:48:50
9 ОТВЕТОВ
РЕШЕНИЕ

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

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

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

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

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

61
23.05.2017 12:34:33
Ну, по крайней мере, я ответил на один вопрос. И я не добавил код, потому что код в вопросе уже показался мне подходящим. Хотя я бы добавил это в транзакцию, я не учел уровень изоляции для обновления. Спасибо за указание на это в вашем ответе!
binOr 31.01.2009 22:46:21

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

7
17.08.2008 07:24:48
и вы должны абсолютно использовать его, а не эту читаемую ерунду ерунду. Хороший пример здесь - mssqltips.com/sqlservertip/1704/…
Rich Bryant 29.11.2017 14:50:47

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

10
24.08.2008 20:21:28

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

3
29.08.2008 21:41:33

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

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

1
16.05.2012 20:31:28

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

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

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

5
2.09.2008 20:13:39

Вам нужно не только запустить его в транзакции, но и высокий уровень изоляции. Фактически, уровень изоляции по умолчанию - 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

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

6
25.01.2018 10:15:53
@Munish Goyal Потому что в базе данных несколько команд и процедур выполняются в параллельном режиме. Затем другой поток может вставить строку сразу после запуска обновления и до запуска вставки.
Tomas Tintera 3.02.2011 21:49:38

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

Для быстрого ответа попробуйте следующий шаблон. Это будет хорошо работать на 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
52
6.06.2011 08:18:37
В своем блоге вы заканчиваете с помощью подсказки WITH (updlock, serializable) в проверке существования. Однако чтение MSDN означает: «UPDLOCK - указывает, что блокировки обновления следует снимать и удерживать до завершения транзакции». Означает ли это, что сериализуемый намек является излишним, поскольку блокировка обновления будет сохраняться в течение оставшейся части транзакции, или я что-то неправильно понял?
Dan Def 23.01.2017 17:08:01

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

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

1
21.10.2012 07:26:35