Replace empty cells with NULL values in large number of columns
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.
You could do a query on syscolumns
to get a list of columns, and use the results to construct your query.
select quotename(name) + ' = nullif (' + quotename(name)+ ','''')'
from syscolumns
where id = object_id('yourtable')
Additionally, if you write your query as
update yourtable
set
yourcolumn=nullif(yourcolumn, ''),
yourcolumn2=nullif(yourcolumn2, ''),
...
then you can do it in a single query without a where clause