Programación en SQL Server - parte 4

---

En esta entrada veremos cómo utilizar la sentencia MERGE para poder actualizar el contenido de una tabla en base a la comparación contra otra tabla.

Supongamos que tenemos una tabla de productos y que nos envían en un archivo de XML los nuevos datos de los productos, en este archivo XML pueden venir productos nuevos y productos existentes que se desean actualizar.

Hay muchas formas de solucionar este problema, a mí se me ocurre la siguiente (en la forma tradicional):

1. Cargar el archivo XML a una tabla temporal.
2. Actualizar los productos que están en la tabla tempora y que existen en la tabla objetivo.
3. Insertar los productos que están en la tabla temporal pero que no existen en la tabla objetivo.

Este trabajo se puede resolver de una forma mucho más compacta con la instrucción MERGE, dado que dependiendo de la existencia de los elementos en la tabla objetivo podemos realizar tareas de inserción, actualización o eliminación.

Vamos a crear las estructuras que utilizaremos para explicar cómo funciona esta instrucción (no he colocado constraints de tipo UNIQUE ni CHECK para hacer más corto el código):

CREATE TABLE Products(
    Id TINYINT NOT NULL,
    Name VARCHAR(10) NOT NULL,
    ListPrice DECIMAL(8, 2) NOT NULL,
    CONSTRAINT pkProducts PRIMARY KEY (Id)
)
GO
INSERT INTO Products (Id, Name, ListPrice)
VALUES (1, 'Product 1', 10),
    (2, 'Product 2', 20),
    (3, 'Product 3', 30),
    (4, 'Product 4', 40)

Ahora voy a mostrar el XML que estaría recibiendo nuestro procedimiento almacenado para realizar la actualización o inserción de productos:

<products>
    <product id="2" name="new 2" listPrice="22" />
    <product id="4" name="new 4" listPrice="44" />
    <product id="5" name="Product 5" listPrice="50" />
</products>

Podemos notar que los productos 2 y 4 ya existen en nuestra tabla y que el producto 5 no, por lo que el resultado esperado es que los productos 2 y 4 sean actualizados y el producto 5 sea insertado en la tabla.

La sentencia MERGE tiene la siguiente sintaxis (reducida):

MERGE INTO Target T
USING Source S
ON T.Col1 = S.Col1
WHEN MATCHED THEN
    --Sentencia a ejecutarse cuando halle correspondencia entre las filas
WHEN NOT MATCHED THEN
    --Sentencia a ejecutarse cuando no halle correspondencia en la tabla Target
WHEN NOT MATCHED BY SOURCE THEN
    --Sentencia a ejecutarse cuando no halle correspondencia en la tabla Source

He colocado como nombres de tabla Target y Source dado que la tabla Target será el objetivo principal de las actividades a realizarse en base a la comparación entre las filas contra la tabla Source en su columna Col1.

En nuestro caso la tabla Target será Products mientras que la tabla Source será el documento XML que estamos recibiendo como parámetro.

La columna que en nuestro caso estaremos comparando para verificar si hay o no correspondencia será Id.

Voy a escribir el código completo para la declaración de la variable de tipo XML (simulando la recepción del parámetro) y la sentencia MERGE:

DECLARE @Products XML = '
<products>
    <product id="2" name="new 2" listPrice="22" />
    <product id="4" name="new 4" listPrice="44" />
    <product id="5" name="Product 5" listPrice="50" />
</products>'

MERGE INTO Products P
USING @Products.nodes('/products/product') T(c)
ON P.Id = c.value('@id', 'TINYINT')
WHEN MATCHED THEN
    UPDATE
    SET Name = c.value('@name', 'VARCHAR(10)'),
        ListPrice = c.value('@listPrice', 'DECIMAL(8, 2)')
WHEN NOT MATCHED THEN
    INSERT (Id, Name, ListPrice)
    VALUES (c.value('@id', 'TINYINT'),
        c.value('@name', 'VARCHAR(10)'),
        c.value('@listPrice', 'DECIMAL(8, 2)')
    );

Si revisamos el contenido de la tabla Products veremos lo siguiente:

Vemos que los productos 2 y 4 fueron actualizados acorde a los valores que tenía nuestro documento XML y que el producto 5 fue agregado con los valores del documento XML.

Notemos que la sentencias UPDATE e INSERT no indican en qué tabla se van a ejecutar, para entenderlo recordemos que la tabla que aparece después de MERGE INTO es la tabla objetivo, por lo tanto es la tabla a la que apuntarán las sentencias establecidas en WHEN MATCHED y WHEN NOT MATCHED.

En la siguiente entrada exploraremos con más detalle la forma en cómo podemos operar con documentos XML, espero te resulte de ayuda esta entrada.