update - Soluciones para INSERTAR O ACTUALIZAR en SQL Server

modificar registro sql server

Question

Supongamos una estructura de tabla de MyTable(KEY, datafield1, datafield2...) .

A menudo quiero actualizar un registro existente o insertar un nuevo registro si no existe.

Esencialmente:

IF (key exists)
  run update command
ELSE
  run insert command

¿Cuál es la mejor manera de escribir esto?

Answer 1

¿Las condiciones de la carrera realmente importan si primero intentas una actualización seguida de un inserto? Digamos que tienes dos hilos que quieren establecer un valor para clave clave :

Hilo 1: valor = 1
Hilo 2: valor = 2

Ejemplo de escenario de condición de carrera

1. la clave no está definida
2. El hilo 1 falla con la actualización
3. El hilo 2 falla con la actualización
4. Exactamente uno de los hilos 1 o 2 tiene éxito con el inserto. Ej. Hilo 1

5. La otra hebra falla con la inserción (con clave duplicada de error) - la hebra 2.

   • Resultado: El "primero" de las dos huellas para insertar, decide el valor.
   • Resultado deseado: el último de los 2 subprocesos para escribir datos (actualizar o insertar) debe decidir el valor

Pero; en un entorno multiproceso, el programador del sistema operativo decide el orden de ejecución del hilo: en el escenario anterior, donde tenemos esta condición de carrera, fue el sistema operativo el que decidió la secuencia de ejecución. Es decir: es incorrecto decir que "subproceso 1" o "subproceso 2" fue "primero" desde un punto de vista del sistema.

Cuando el tiempo de ejecución es tan cercano para el subproceso 1 y el subproceso 2, el resultado de la condición de carrera no importa. El único requisito debe ser que uno de los hilos defina el valor resultante.

Para la implementación: si la actualización es seguida por los resultados de inserción en el error "clave duplicada", esto debe tratarse como un éxito.

Además, uno nunca debe asumir que el valor en la base de datos es el mismo que el último que escribió.

Answer 2

Antes de que todos salten a HOLDLOCK-s por temor a que estos nafarios usuarios ejecuten sus programas directamente :-) permítanme señalarles que deben garantizar la exclusividad de los nuevos PK-s por diseño (claves de identidad, generadores de secuencias en Oracle, índices únicos para ID-s externas, consultas cubiertas por índices). Ese es el alfa y omega del tema. Si no tiene eso, ningún HOLDLOCK-s del universo lo salvará y si lo tiene, entonces no necesita nada más que UPDLOCK en la primera selección (o para usar la actualización primero).

Los Sprocs normalmente se ejecutan bajo condiciones muy controladas y con el supuesto de un llamador de confianza (nivel medio). Lo que significa que si un simple patrón de incremento (actualización + inserción o combinación) ve un PK duplicado significa un error en el diseño de la tabla o nivel medio y es bueno que SQL grite una falla en tal caso y rechace el registro. La colocación de un HOLDLOCK en este caso equivale a excepciones de alimentación y toma de datos potencialmente defectuosos, además de reducir su rendimiento.

Dicho esto, usar MERGE, o ACTUALIZAR, entonces INSERTAR es más fácil en su servidor y menos propenso a errores ya que no tiene que recordar agregar (UPDLOCK) para seleccionarlo primero. Además, si está realizando inserciones / actualizaciones en pequeños lotes, necesita conocer sus datos para decidir si una transacción es apropiada o no. Es solo una recopilación de registros no relacionados, por lo que la transacción adicional "envolvente" será perjudicial.

Answer 3

Aunque es bastante tarde para comentar sobre esto, quiero agregar un ejemplo más completo usando MERGE.

Estas instrucciones Insertar + Actualizar se denominan generalmente declaraciones "Upsert" y pueden implementarse utilizando MERGE en SQL Server.

Aquí se da un muy buen ejemplo: weblogs.sqlteam.com/dang/archive/2009/01/31/…

Lo anterior explica también los escenarios de bloqueo y concurrencia.

Citaré lo mismo como referencia:

ALTER PROCEDURE dbo.Merge_Foo2
      @ID int
AS

SET NOCOUNT, XACT_ABORT ON;

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
      ON f.ID = new_foo.ID
WHEN MATCHED THEN
    UPDATE
            SET f.UpdateSpid = @@SPID,
            UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT
      (
            ID,
            InsertSpid,
            InsertTime
      )
    VALUES
      (
            new_foo.ID,
            @@SPID,
            SYSDATETIME()
      );

RETURN @@ERROR;

Answer 4

En SQL Server 2008 puedes usar la sentencia MERGE

Answer 5

Eso depende del patrón de uso. Uno tiene que mirar el panorama general de uso sin perderse en los detalles. Por ejemplo, si el patrón de uso es el 99% de las actualizaciones después de que se haya creado el registro, entonces ''UPSERT'' es la mejor solución.

Después de la primera inserción (hit), serán todas las actualizaciones de instrucciones únicas, sin ifs ni buts. La condición ''dónde'' en la inserción es necesaria, de lo contrario insertará duplicados y no querrá lidiar con el bloqueo.

UPDATE <tableName> SET <field>=@field WHERE key=@key;

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

Answer 6

Hacer un UPSERT:

UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key IF @@ROWCOUNT = 0 INSERT INTO MyTable (FieldA) VALUES (@FieldA)

http://en.wikipedia.org/wiki/Upsert

Answer 7

Hacer una si existe ... de lo contrario ... implica hacer dos solicitudes como mínimo (una para marcar, una para tomar acción). El siguiente enfoque requiere solo uno donde exista el registro, dos si se requiere una inserción:

DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = ''xxx'', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
  INSERT INTO MyTable (Key, DataField1) VALUES (123, ''xxx'')

Answer 8

Haga una selección, si obtiene un resultado, actualícelo, si no, créelo.

Answer 9

Intenté la solución a continuación y funciona para mí, cuando se produce una solicitud concurrente para la declaración de inserción.

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert table (key, ...)
   values (@key, ...)
end
commit tran

Answer 10

MS SQL Server 2008 presenta la declaración MERGE, que creo que es parte del estándar SQL: 2003. Como muchos han demostrado, no es un gran problema manejar casos de una fila, pero cuando se trata de grandes conjuntos de datos, se necesita un cursor, con todos los problemas de rendimiento que se presentan. La declaración MERGE será una adición muy bienvenida cuando se trate de grandes conjuntos de datos.

Answer 11

Muchas personas sugerirán que uses MERGE , pero te advierto que no lo hagas. Por defecto, no te protege de las condiciones de concurrencia y de carrera más que varias declaraciones, pero presenta otros peligros:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Incluso con esta sintaxis "más simple" disponible, sigo prefiriendo este enfoque (el manejo de errores se omite por brevedad):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

Mucha gente sugerirá de esta manera:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
  INSERT ...
END
COMMIT TRANSACTION;

Pero todo lo que se logra es asegurarse de que necesite leer la tabla dos veces para ubicar las filas que se actualizarán. En la primera muestra, solo tendrá que ubicar las filas una vez. (En ambos casos, si no se encuentran filas desde la lectura inicial, se produce una inserción).

Otros sugerirán de esta manera:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

Sin embargo, esto es problemático si, por el solo hecho de permitir que SQL Server detecte las excepciones que pudo haber evitado, es mucho más costoso, excepto en el raro escenario en el que casi todas las inserciones fallan. Lo demuestro mucho aquí:

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

Answer 12

No te olvides de las transacciones. El rendimiento es bueno, pero el enfoque simple (si existe) es muy peligroso.
Cuando varios subprocesos intentarán realizar Insertar o actualizar, puede obtener fácilmente la violación de la clave principal.

Las soluciones proporcionadas por @Beau Crawford y @Esteban muestran una idea general pero propensa a errores.

Para evitar puntos muertos y violaciones de PK, puedes usar algo como esto:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

o

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Answer 13

Por lo general, hago lo que varios de los otros carteles han dicho con respecto a verificar si existe primero y luego hacer lo que sea correcto. Una cosa que debe recordar al hacer esto es que el plan de ejecución almacenado en caché por sql podría no ser óptimo para una u otra ruta. Creo que la mejor manera de hacer esto es llamar a dos procedimientos almacenados diferentes.

FirstSP: If Exists Call SecondSP (UpdateProc) Else Call ThirdSP (InsertProc)

Ahora, no sigo mi propio consejo muy a menudo, así que tómalo con un grano de sal.

Answer 14

Puede usar la instrucción MERGE . Esta declaración se usa para insertar datos si no existen o actualizarlos si existen.

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

Answer 15

Puede utilizar esta consulta. Trabajar en todas las ediciones de SQL Server. Es simple y claro. Pero necesitas usar 2 consultas. Puedes usar si no puedes usar MERGE

    BEGIN TRAN

    UPDATE table
    SET Id = @ID, Description = @Description
    WHERE Id = @Id

    INSERT INTO table(Id, Description)
    SELECT @Id, @Description
    WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)

    COMMIT TRAN

NOTA: Por favor explique las respuestas negativas.

Answer 16

Si desea UPSERT más de un registro a la vez, puede usar la sentencia DML ANSI SQL: 2003 DML.

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Consulte la declaración de MERGE Mimicking en SQL Server 2005 .

Answer 17

Si usa ADO.NET, el DataAdapter maneja esto.

Si quieres manejarlo tú mismo, esta es la forma:

Asegúrese de que haya una restricción de clave principal en su columna de clave.

Entonces tú:

1. Hacer la actualización
2. Si la actualización falla porque ya existe un registro con la clave, haga la inserción. Si la actualización no falla, ya ha terminado.

También puede hacerlo al revés, es decir, hacer la inserción primero y hacer la actualización si la inserción falla. Normalmente, la primera forma es mejor, porque las actualizaciones se realizan con más frecuencia que las inserciones.

Answer 18

Si va a ACTUALIZAR if-no-rows-updated y luego INSERT route, considere hacer el INSERT primero para evitar una condición de carrera (suponiendo que no interviene DELETE)

INSERT INTO MyTable (Key, FieldA)
   SELECT @Key, @FieldA
   WHERE NOT EXISTS
   (
       SELECT *
       FROM  MyTable
       WHERE Key = @Key
   )
IF @@ROWCOUNT = 0
BEGIN
   UPDATE MyTable
   SET FieldA=@FieldA
   WHERE Key=@Key
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END

Además de evitar una condición de carrera, si en la mayoría de los casos el registro ya existirá, esto causará que INSERT falle, desperdiciando la CPU.

Usando MERGE probablemente es preferible para SQL2008 en adelante.

Answer 19

Ver mi respuesta detallada a una pregunta anterior muy similar

@Beau Crawford''s es una buena forma en SQL 2005 y más adelante, aunque si está otorgando una representación, debe ser la primera persona que la TENGA . El único problema es que para las inserciones aún son dos operaciones IO.

MS Sql2008 introduce la merge desde el estándar SQL: 2003:

merge tablename with(HOLDLOCK) 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, ... )

Ahora es realmente solo una operación IO, pero un código horrible :-(

Answer 20

/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/

DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0

MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO

Reemplace los nombres de tablas y campos por lo que necesite. Tenga cuidado de la condición de uso ON . Luego, establezca el valor apropiado (y el tipo) para las variables en la línea DECLARAR.

Aclamaciones.

Answer 21

IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

Editar:

Por desgracia, incluso en mi propio detrimento, debo admitir que las soluciones que hacen esto sin una selección parecen ser mejores, ya que cumplen la tarea con un paso menos.