resueltos - procedimiento almacenado sql server insert

Insertar proceso almacenado de actualización en SQL Server

Question

He escrito un proceso almacenado que hará una actualización si existe un registro, de lo contrario, hará una inserción. Se ve algo como esto:

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

Mi lógica detrás de escribirlo de esta manera es que la actualización realizará una selección implícita utilizando la cláusula where y si eso devuelve 0, entonces la inserción tendrá lugar.

La alternativa para hacerlo de esta manera sería hacer una selección y luego, en función del número de filas devueltas, haga una actualización o inserte. Esto lo considero ineficiente porque si va a hacer una actualización, causará 2 selecciones (la primera llamada de selección explícita y la segunda implícita en el lugar de la actualización). Si el proceso fuera a hacer una inserción, entonces no habría diferencia en la eficiencia.

¿Mi lógica suena aquí? ¿Es así como combinarías una inserción y una actualización en un proceso almacenado?

Answer 1

Gran entusiasta de UPSERT, realmente reduce el código para administrar. Aquí hay otra forma en que lo hago: uno de los parámetros de entrada es ID, si el ID es NULL o 0, usted sabe que es un INSERT, de lo contrario es una actualización. Asume que la aplicación sabe si hay una identificación, por lo que no funcionará en todas las situaciones, pero cortará los ejecutables a la mitad si lo hace.

Answer 2

MERGE es una de las nuevas características de SQL Server 2008, por cierto.

Answer 3

No solo necesita ejecutarlo en una transacción, sino que también necesita un alto nivel de aislamiento. De hecho, el nivel de aislamiento predeterminado es Read Commited y este código necesita 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

Quizás agregar también la comprobación de error @@ y la reversión podrían ser una buena idea.

Answer 4

Por favor, lea la publicación en mi blog para un patrón bueno y seguro que puede usar. Hay muchas consideraciones, y la respuesta aceptada sobre esta cuestión está lejos de ser segura.

Para una respuesta rápida pruebe el siguiente patrón. Funcionará bien en SQL 2000 y superior. SQL 2005 le proporciona un manejo de errores que abre otras opciones y SQL 2008 le da un comando 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 5

Publicación modificada de Dima Malenko:

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 

Puede interceptar el error y enviar el registro a una tabla de inserción fallida.
Necesitaba hacer esto porque tomamos los datos que se envían a través de WSDL y, si es posible, los arreglamos internamente.

Answer 6

Si no está haciendo una combinación en SQL 2008, debe cambiarla a:

if @@ rowcount = 0 y @@ error = 0

de lo contrario, si la actualización falla por alguna razón, entonces intentará y luego se insertará porque el recuento de filas en una declaración fallida es 0

Answer 7

Si se va a utilizar con SQL Server 2000/2005, el código original debe incluirse en la transacción para garantizar que los datos permanezcan consistentes en el escenario concurrente.

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

Esto generará un costo de rendimiento adicional, pero garantizará la integridad de los datos.

Agregar, como ya se sugirió, MERGE debería usarse donde esté disponible.

Answer 8

Su lógica parece sólida, pero es posible que desee considerar agregar algún código para evitar la inserción si hubiera pasado una clave primaria específica.

De lo contrario, si siempre está haciendo una inserción si la actualización no afectó ningún registro, ¿qué sucede cuando alguien borra el registro antes de ejecutar "UPSERT"? Ahora el registro que intentabas actualizar no existe, por lo que creará un registro. Ese probablemente no es el comportamiento que estabas buscando.

Answer 9

Su suposición es correcta, esta es la forma óptima de hacerlo y se llama upsert/merge .

Importancia de UPSERT - desde sqlservercentral.com :

Para cada actualización en el caso mencionado anteriormente, estamos eliminando una lectura adicional de la tabla si utilizamos UPSERT en lugar de EXISTS. Desafortunadamente para un inserto, tanto los métodos UPSERT como IF EXISTS utilizan el mismo número de lecturas en la tabla. Por lo tanto, la verificación de la existencia solo debe hacerse cuando existe una razón muy válida para justificar la E / S adicional. La forma optimizada de hacer las cosas es asegurarse de que tenga pocas lecturas como sea posible en la base de datos.

La mejor estrategia es intentar la actualización. Si ninguna fila se ve afectada por la actualización, inserte. En la mayoría de las circunstancias, la fila ya existirá y solo se requerirá una E / S.

Editar : compruebe esta respuesta y la publicación del blog vinculada para conocer los problemas con este patrón y cómo hacer que funcione de forma segura.