I have SQL table that has a large number of columns. For some reason, some columns have empty cells instead of NULL cells. I would like to make all empty cells in all the columns to be NULL.
I know that the way to go for a single column is:
UPDATE your_table SET column = NULL WHERE column = ''
Run the following query:
SELECT 'UPDATE yourtable SET ' + name + ' = NULL WHERE ' + name + ' = '''';' FROM syscolumns WHERE id = object_id('yourtable') AND isnullable = 1;
The output of this query will be a chunk of SQL script like this:
UPDATE yourtable SET column1 = NULL WHERE column1 = ''; UPDATE yourtable SET column2 = NULL WHERE column2 = ''; UPDATE yourtable SET column3 = NULL WHERE column3 = ''; -- etc...
Copy and paste that SQL script into a new query and run it to update all your columns.