Tips para realizar UPSETs o updates con insert en SQL Server

En muchas ocasiones, especialmente en procesos ETL o de carga de datos para un data warehouse, por ejemplo, interesa hacer en una sola sentencia o en un solo paso la comprobación de si un registro existe, y si existe actualizarlo, y si no insertarlo. A esta combinación se le ha apodado UPSET, aunque en SQL existe una sentencia específica para hacerlo, que es MERGE.

Reúno en este post algunas soluciones sencillas para hacer, con SQL Server, INSERTS o UPDATES en función de la existencia o no de los registros entrantes. Cambiando un poco la sintaxis se puede hacer lo mismo en otros motores de base de datos, como Oracle, o MySQL.

UPSET en SQL Server

La intrucción SQL que permite 'mezclar' datos de dos tablas en una es MERGE. Tiene la particularidad de que su sintaxis permite especificar una sentencia SQL para el caso de que el registro que se va a insertar ya exista (si hay coincidencia en los campos que se definan), y otra sentencia diferente en el caso de que no haya ningún registro en la tabla destino con coincidencia de valores en los campos especificados. Lo normal es definir un update en el caso de que el registro ya exista y un insert en el caso contrario.

Merge para actualizar/insertar un registro

Quedará más claro con este ejemplo de cómo Insertar o actualizar valores fijos para un registro en concreto, cosa que podría ser útil, en un entorno DWH for example, para insertar en una dimensión el registro ficticio correspondiente a id's no encontrados (o dummy):

MERGE Tabla_Destino WITH(HOLDLOCK) as target
using (values ('valor1', 'valor2'))
    as source (campo1, campo2)
    on target.idregistro = 23
when matched then
    update
    set campo1_dest = source.campo1,
        campo2_dest = source.campo2
when not matched then
    insert ( idregistro, campo1_dest, campo2_dest)
    values ( 23, source.campo1, source.codempresa);

Si el registro con idregistro=23 existe, se actualizan los campos campo1_dest y campo2_dest con los valores 'valor1' y 'valor2', respectivamente.
Si no existe, se inserta un registro con idregistro=23, valor1_dest='valor1' y valor2_dest='valor2' 

Transacciones para actualizar/insertar un registro

Otra solución sencilla para insertar/actualizar un registro, pero utilizando transacciones en lugar de la instrucción MERGE sería la siguiente:

(Con un cursor y parámetros se puede implementar fácilmente para actualizar/insertar más registros)

BEGIN TRAN
  SELECT idRegistro FROM Tabla_Destino WHERE idRegistro=23
 IF @@ROWCOUNT = 0
     insert ( idregistro, campo1_dest, campo2_dest)
     values ( 23, ‘valor1’, ‘valor2’);
COMMIT TRAN

Se hace un update de la tabla. Si no se ha actualizado es que no existe el registro, y entonces se hace un insert. 

Merge para actualizar/insertar varios registros

El merge anterior insertaba un sólo registro, pero MERGE sirve para tomar como origen de datos tablas completas, o simplemente registros resultado de cualquier sentencia de selección (SELECT), con lo que podemos crear sentencias más potentes, para ejecutar eficientes operaciones que trabajan a nivel de conjunto, y que normalmente van a ser más rápidas que opciones como la transacción anterior.

Por ejemplo, para Insertar o actualizar valores que provengan de una tabla o vista:

MERGE Tabla_Destino WITH(HOLDLOCK) as target
using (select idregistro, campo1, campo2 from Tabla_Origen)
    as source (campo1, campo2)
    on (target.idregistro = source.idregistro)
when matched then
    update
    set campo1_dest = source.campo1,
        campo2_dest = source.campo2
when not matched then
    insert ( idregistro, campo1, campo2)
    values ( source.idregistro, source.campo1, source.campo2);

Referencias

Finalizo enlazando algunas referencias para el que quiera ampliar información:

• Información sobre MERGE y UPSERT, en Wikipedia
  MERGE (SQL)

 

• Soluciones para INSERT o UPDATE, en Stackoverflow
  Solutions for INSERT or UPDATE on SQL Server

 

• Comparativa de rendimiento en SQL Server para sentencias MERGE, en MSSQLTips
  Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

 

• Sintaxis de la sentencia MERGE, en la librería Microsoft MSDN
  MERGE (Transact-SQL)

 

• Cómo utilizar MERGE en Oracle, y como evitar un error muy común:
  UPDATE con JOIN en Oracle
  El error ORA-30926 como resultado de una operación Merge