I tell you, I am developing a table validator. First I create the table with the columns I need, then I make some updates with information that will be repeated in the columns.
However, the last section of updates, which is where I update the column named NUMERADOR
for each of my primary keys ID_KQI
, I have to validate if the field of one of my tables has nulls and if it does, I have to put it in that record. So far that does great and I have the output I expect.
However, I am concerned, is there a way to put all these updates in a loop within SQL Server?
CREATE TABLE Parametria.dbo.TABLA_INPUT
(ID_KQI varchar(100),
FECHA_PROCESO date,
FECHA_CONTABLE date,
NUMERADOR int,
POBLACION_TOTAL int,
ID_PRODUCTO varchar(10),
ID_ENTIDAD varchar(4));
INSERT INTO Parametria.dbo.TABLA_INPUT (ID_KQI,POBLACION_TOTAL, NUMERADOR)
SELECT ID_KQI, (select COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION]) AS POBLACION_TOTAL,
(SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE CLIENTE IS NULL AND ID_KQI = '001-RCDAT_D001-1') AS NUMERADOR
FROM Parametria.dbo.FinRep A
WHERE NOMBRE_SISTEMA = 'CALIFICACION_IBM_TC' AND ID_TIPO_KQI = '001'
UPDATE Parametria.dbo.TABLA_INPUT SET ID_PRODUCTO = 'NA'
--UPDATE Parametria.dbo.TABLA_INPUT SET FECHA_CONTABLE = 'NA'
UPDATE Parametria.dbo.TABLA_INPUT SET FECHA_PROCESO = GETDATE()
UPDATE Parametria.dbo.TABLA_INPUT SET ID_ENTIDAD = 9157
--UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = count(*) FROM FINREP.dbo.['Cartera TC']
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [No# LINEA] IS NULL) WHERE ID_KQI = '001-RCDAT_D002-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [No# CLIENTE] IS NULL) WHERE ID_KQI = '001-RCDAT_D003-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [TIPO DE GARANTIA 1] IS NULL) WHERE ID_KQI = '001-SADAT_D004_1-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [TIPO DE GARANTIA 2] IS NULL) WHERE ID_KQI = '001-SADAT_D004_2-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [TIPO DE GARANTIA 3] IS NULL) WHERE ID_KQI = '001-SADAT_D004_3-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [TIPO DE GARANTIA 4] IS NULL) WHERE ID_KQI = '001-SADAT_D004_4-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [TIPO DE GARANTIA 5] IS NULL) WHERE ID_KQI = '001-SADAT_D004_5-1';
--UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [PARI PASSU NOMBRE GARANTE] IS NULL) WHERE ID_KQI = '001-SADAT_D009-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [(B)+(C)+(D)+(E)+(F)= RESERVAS TOTALES] IS NULL) WHERE ID_KQI = '001-RCDAT_M002-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [$ GARANTIA 1] IS NULL) WHERE ID_KQI = '001-SADAT_M003_1-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [$ GARANTIA 2] IS NULL) WHERE ID_KQI = '001-SADAT_M003_2-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [$ GARANTIA 3] IS NULL) WHERE ID_KQI = '001-SADAT_M003_3-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [$ GARANTIA 4] IS NULL) WHERE ID_KQI = '001-SADAT_M003_4-1';
UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [$ GARANTIA 5] IS NULL) WHERE ID_KQI = '001-SADAT_M003_5-1';
--UPDATE Parametria.dbo.TABLA_INPUT SET NUMERADOR = (SELECT COUNT(*) FROM FINREP.dbo.[CALIFICACION_IBM_TC.CALIFICACION] WHERE [PARI PASSU $ GARANTIA] IS NULL) WHERE ID_KQI = '001-SADAT_M010-1';
SELECT * FROM Parametria.dbo.TABLA_INPUT
This is a way to do everything in one statement. It does not require subsequent UPDATEs because all data is inserted from the beginning. For that,