Replace all NULL fields in a table to 0 SQL Server

Question

Replace all NULL fields in a table to 0 SQL Server

Navigation

#1 by (2 votes)

2

Greetings to all, I have a table that contains about 97 fields. I know that by selecting the fields and adding functions like Coalense, isnull or null; can be converted to 0. However my question is a bit complex. Is there any way to identify in the table all the fields that contain nulls and in turn replace them with 0?

I clarify that some fields have nulls and numbers, that is, the scrambled data comes.

----zonas
    declare @temporalzonas  table(
        unitsysid varchar(20), 
        licenseplate varchar(50),
        zona varchar(30),
        cedis varchar(30)
    ) 
    insert @temporalzonas 
    exec sp_unidades_operativas_femsa   
    --select * from @temporalzonas
---------------------------------------------

-----------TELEMETRIA
    declare @TemporalTelemetria table
    (
        zona varchar(35),
        cedis varchar(35),
        udidlog varchar(15),
        tclientes int,
        tvisitados int,
        tnovisitados int,
        validacion_cliente varchar(50),
        clientes_fin_firma_entrega int,
        cajas_sin_firma int,
        cajas_entregadas int,
        cajas_rechazadas int,
        validacion_caja varchar(30),
        tcajas int,
        total_cajas_validacion int /*,
        validacion_cajas varchar (50)*/
    )

    insert @TemporalTelemetria

    select zona,
           cedis,
           udid,
           tclientes, 
           tvisitados, 
           tnovisitados, 

           (case
                when tclientes = tvisitados then 'Visito todos los clientes'
                else 'No completo las visitas'
            end) 
            validacion_cliente,

            clientes_sin_firma_entrega, coalesce(cajas_sin_firma,0) cajas_sin_firma,
           cajas_entregadas, 
           coalesce(cajas_rechazadas,0) cajas_rechazadas, 

           (case
                when (cajas_entregadas + (isnull(cajas_rechazadas,0)) + cajas_sin_firma) = tcajas then 'Correcta' 
                else 'Validar caja'
            end        
           )
           validacion_caja,


           tcajas,
           (cajas_entregadas + coalesce(cajas_rechazadas,0)) total_cajas_validacion        
    from Telemetria 
           inner join @temporalzonas on 
            unitsysid = udid
            select * from @TemporalTelemetria

sql

asked by Ric_hc 28.04.2017 в 19:51

source

1 answer

Error creating table in MySQL Inventory Management in C ++

score 2 · Accepted Answer

Assuming that all fields are numeric, conceptually it is something like this:

update tabla
    set campo1 = isnull(campo1,0),
    campo2 = isnull(campo2,0),
    ...
    campoN = isnull(campoN,0)
    WHERE campo1 IS NULL OR
          campo2 IS NULL OR
          ...
          campoN IS NULL

Now if your question points to what you do not want to write the 97 fields, well you could but it is much more complex, you should do a dynamic query, see all the numeric fields in the table and build a query like the from above

In the case of wanting to make a dynamic query, with this you can generate the Script, then execute it with sp_executesql

DECLARE @SQL        NVARCHAR(MAX)
DECLARE @UPDATE     NVARCHAR(MAX)
DECLARE @WHERE      NVARCHAR(MAX)
DECLARE @TableName  VARCHAR(255)

SELECT  @TableName  = 'NOMBRE_DE_TABLA'
SELECT  @UPDATE     = ''
SELECT  @WHERE      = ''

SELECT  @UPDATE = @UPDATE + '       ' + c.name + ' = ISNULL(' + c.name +',0),'  + CHAR(10),
    @WHERE  = @WHERE + '              OR ' + c.name + ' IS NULL'            + CHAR(10)
    FROM sys.columns c
    INNER JOIN sys.types t 
        ON c.user_type_id = t.user_type_id
    WHERE
            c.object_id = OBJECT_ID(@TableName)
        AND t.name IN ('tinyint','smallint','int','real','money','float',
                'decimal','numeric','smallmoney','bigint')



IF LEN(@UPDATE)> 0 
BEGIN
    SELECT @SQL =   'UPDATE ' + @TableName + ' SET'         + CHAR(10) +
            '' + LEFT(@UPDATE,LEN(@UPDATE)-1)   + CHAR(10) +
            '       WHERE  1 = 2 '              + CHAR(10) +
            @WHERE
    PRINT @SQL
END

Clarifications

Does not work with variable type tables @Tabla
Yes, it works with all types of temporary, #Table or ## Table, but it must be borne in mind that in these cases it is necessary to point to the sycolumns and systypes of the Tempdb