IF EXISTS (SELECT 1 FROM miTabla WHERE llave = ...)
BEGIN
UPDATE miTabla SET ...
END
ELSE BEGIN
INSERT INTO miTabla ...
END
La estructura MERGE, introducida en el estándar de SQL:2003 (el estándar, no el producto de MS), y por Microsoft en SQL Server 2008 (aunque con varios problemas) nos ayuda precisamente a atacar relacionalmente este tipo de problemas.
Planteemos un caso sencillo. Primero aclarar que estoy trabajando con SQL SERVER 2014. Supongamos que tenemos una sencilla tabla de productos, y que cada cierto tiempo se reabastecen las existencias, pero en este proceso de carga de reabastecimiento pueden venir tanto productos existentes como totalmente nuevos. El requisito a cumplir es que si al cargar el producto éste ya existe se actualice la descripción del mismo así como la cantidad existente, sumando a la cantidad en el producto la cantidad a reabastecer y, si no existe, se inserte con la cantidad indicada en la carga. La forma para saber si el producto existe es por comparación del código del mismo. Simple.
Atacamos el problema, en primera instancia, cargando los reabastecimientos en una tabla de carga. Esto se puede realizar por medio de un bulk copy explicado en este post.
Las dos tablas necesarias las definimos de la siguiente manera:
CREATE TABLE Producto
(
conProducto INT NOT NULL IDENTITY (1,1),
cocProducto VARCHAR(5) NOT NULL
CONSTRAINT Producto_cocProducto_UK UNIQUE,
desProducto VARCHAR(150),
numCantidad INT NOT NULL
CONSTRAINT Producto_numCantidad_DF DEFAULT (0),
fecIngreso DATETIME NOT NULL
CONSTRAINT Producto_fecingreso_DF DEFAULT (getdate()),
CONSTRAINT Producto_PK
PRIMARY KEY CLUSTERED (conProducto)
WITH (IGNORE_DUP_KEY = OFF)
);
CREATE TABLE ProductoCarga
(
cocProducto VARCHAR(5) NOT NULL,
desProducto VARCHAR(150),
numCantidad INT NOT NULL
CONSTRAINT ProductoCarga_numCantidad_DF DEFAULT (0)
);
Ahora necesitamos un procedimiento almacenado que tome los datos de ProductoCarga y le aplique la lógica requerida para pasarlos a Producto y que además limpie la tabla de carga para dejarla lista para el próximo reabastecimiento. Sería algo similar a esto:
CREATE PROCEDURE spCargaProductos
AS
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
MERGE Producto WITH (HOLDLOCK) AS TGT
USING (SELECT PC.cocProducto, desProducto, numCantidad
FROM productoCarga PC WITH (HOLDLOCK))
AS SRC (cocProducto, desProducto, numCantidad)
ON (TGT.cocProducto = SRC.cocProducto)
WHEN MATCHED THEN
UPDATE SET TGT.desProducto = SRC.desProducto,
TGT.numCantidad = (TGT.numCantidad + SRC.numCantidad)
WHEN NOT MATCHED THEN
INSERT (cocProducto, desProducto, numCantidad, fecIngreso)
VALUES (SRC.cocProducto, SRC.desProducto, SRC.numCantidad,
getdate());
TRUNCATE TABLE productoCarga
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Primero usamos la palabra reservada MERGE para indicar que vamos a hacer varias operaciones, a continuación viene nuestra tabla destino, o sea, donde vamos a realizar las inserciones o actualizaciones (como es en este ejemplo, también se puede eliminar si fuese necesario) aquí ponemos nuestra tabla Producto y lo decoramos con WITH (HOLDLOCK) para asegurar el lock sobre la tabla mientras estemos trabajando sobre la misma, esto es un seguro en caso de concurrencia, y le asignamos un alias (yo elegí TGT de “Target”).
Seguidamente usamos USING para indicar de donde tomaremos los datos, cuál es su origen. Esto es muy similar a una CTE (Common Table Expression) por lo que una vez definido nuestra consulta sobre la tabla de ProductoCarga le asignamos un alias (yo elegí SRC de “Source”) que debe incluir los nombres de las columnas tal y como las vamos a referenciar en lo subsiguiente.
En la sentencia ON, debemos especificar el criterio con el que determinamos si los registros del origen corresponden con los del destino, en nuestro caso si el código del producto de ambas tablas es el mismo.
La magia viene a continuación. Con WHEN MATCHED THEN, decimos cuando el criterio especificado anteriormente se cumpla realice la operación siguiente y WHEN NOT MACHED THEN, como ya habremos deducido, significa que si el criterio no se cumplió realice lo que sigue a continuación. En el primer caso aplicamos el UPDATE y en el segundo el INSERT, en sendos, como se habrá notado, no se incluye la tabla porque para eso la indicamos con la cláusula MERGE. La parte MATCHED es bastante flexible y puede contener mas lógica, por ejemplo se puede indicar que además de cumplir el criterio del la sentencia ON si la cantidad es mayor a tal número actualice de una manera y otra sentencia MATCHED para que en caso de que sea menor o igual realice la actualización de otra manera
Finalmente la instrucción debe terminar con un punto y coma (;) o nos dará el error 10713. Ademas no se debe olvidar limpiar la tabla de carga, lo cual con un TRUNCATE es mas rápido.
Aunque parezca obvio es necesario tomar en cuenta que el usuario que realice la operación debe tener permisos de consulta sobre la tabla o las tablas de origen y de UPDATE, INSERT o DELETE sobre la tabla destino según se necesite.
Realicemos un par de pruebas, primero insertamos unos registros en la tabla de carga, luego seleccionemos la misma tabla y la tabla de productos, seguido ejecutemos el procedimiento almacenado y a continuación de nuevo las dos consultas para verificar que todo se haya llevado a cabo.
INSERT INTO ProductoCarga
(cocProducto, desProducto,numCantidad)
VALUES ('AA001','Produto de Prueba 1',100),
('AA002','Produto de Prueba 2', 50),
('AA003','Produto de Prueba 3', 10)
SELECT * FROM ProductoCarga
SELECT * FROM Producto
EXECUTE spCargaProductos
SELECT * FROM ProductoCarga
SELECT * FROM Producto
INSERT INTO ProductoCarga
(cocProducto, desProducto,numCantidad)
VALUES ('AA001','Producto de Prueba 1',15),
('AA002','Producto de Prueba 2',8),
('AA003','Producto de Prueba 3',5),
('AA004','Producto de Prueba 4',10),
('AA005','Producto de Prueba 5',40)
SELECT * FROM ProductoCarga
SELECT * FROM Producto
EXECUTE spCargaProductos
SELECT * FROM ProductoCarga
SELECT * FROM Producto
Finalmente dejo algos recursos de interés
Documentación
https://msdn.microsoft.com/es-es/library/bb510625(v=sql.120).aspx
Problemas
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
Desarrallador y líder técnico, con experiencia en tecnologías Microsoft desde los tiempos del VB6 y el asp clásico hasta el .Net Core, pasando por COM+, javascript, angularjs, Ionic, xaml, cordova, MVC, Web Api, Sql Server, Oracle... . Ávido lector, apasionado programador.
0 comments:
Publicar un comentario