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 = ''
However, I am not sure how to execute a similar logic efficiently for all columns without having to write the column names one by one.
Thanks,
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.
using (MyDataContext db = new MyDataContext()) { string queryBuilder = "...first query from above..."; var resultsetOfUpdateQueries = db.ExecuteQuery<string>(queryBuilder); foreach (string oneUpdateQuery in resultsetOfUpdateQueries) { db.ExecuteCommand(oneUpdateQuery); } } No worries about SQL injection because the only variable components of the dynamic SQL statement will all be column names from a dictionary table.
bulk insert from a csv file to populate my SQL table. For some odd reason, some of the empty cells in the csv file got translated as NULL in the table, while others got translated as empty cells in table. The transformation above is to make all the empty tables unified.
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
nullif. That wouldn't be very efficient.
automatic passing of the results of the query to the second query. It is quite obvious I wasn't asking for a trivial copy/paste.I actually use Robert N's answer above daily when I'm importing flat file data sets, so I put it into a stored procedure that I could pass a table name to. It just populates a temp table with the update statements, then executes each row in the table.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LikeableBias
-- Create date: 2016-06-27
-- Description: Finds and NULLs all blank values in table where column allows nulls
-- =============================================
CREATE PROCEDURE [dbo].[sproc_NullBlanks]
@tablename NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
--------Insert update statements to temp table for execution
DECLARE @statements TABLE (statement NVARCHAR(MAX))
INSERT INTO @statements
( statement )
SELECT ('UPDATE '+@tablename+' SET [' + name + '] = NULL WHERE ' + name + ' = '''';')
FROM syscolumns
WHERE id = OBJECT_ID(@tablename)
AND isnullable = 1;
--------Open cursor, execute statements, then close cursor
DECLARE @statement NVARCHAR(MAX)
DECLARE cur CURSOR LOCAL FOR
SELECT statement FROM @statements
OPEN cur
FETCH NEXT FROM cur INTO @statement
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC sys.sp_executesql @statement
FETCH NEXT FROM cur INTO @statement
END
CLOSE cur
DEALLOCATE cur
END
GO
