CODEWITHSUNDEEP
sqlsql-serverstored-procedures
raji g
raji g

Reputation: 1

How to update all the column values with empty strings to null in stored procedure?

I want to update all the empty strings in my whole database with null? I have used select into #temptable in stored procedure to extract values from source to target. But there are many empty strings in the database. Now i need to update those empty strings with Null. Is there any option to update all the columns at once in stored procedure. Please help me

Upvotes: 0

Views: 1238

Answers (2)

Zeki Gumus
Zeki Gumus

Reputation: 1484

You can execute this result. It will give you the list of only nullable strings columns with update statement : 

SELECT
    'UPDATE ' +QUOTENAME(C.TABLE_SCHEMA)+'.'+ QUOTENAME(C.TABLE_NAME) + ' SET ' +  QUOTENAME(C.COLUMN_NAME) + '=NULL WHERE ' + QUOTENAME(C.COLUMN_NAME) + '='''';'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE LIKE '%CHAR'
AND C.IS_NULLABLE = 'YES'

Upvotes: 1

fireshark519
fireshark519

Reputation: 175

what you can do is something like this....very consuming but might help:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = ''



    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT distinct ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

This will show you where (tables and columns) you have empty string values (remember that there is different kinds of...empty string)

then just go through each table and run this

Update myTable set MyColumn = NULL where Field = ''

you can reuse it by not dropping the #results table and adding a row_number to it and then create a looped sequence to get things updated in sequence as well. Hope this somehow helps, but that's probably what I would do.

Upvotes: 0

Related Questions