Replacing Null values in SQL Server 2012 from SSIS Package

Question

I have an SSIS Package that reads in data from a flat file and writes it to a sql server 2012 database. The data contains null values for some fields. I would like to retain the rows that have null values but replace them with default values without having to do a conversion on every column (there are hundreds of columns across a number of tables).

Are there any sql commands/scripts to update any null across a set of tables with a default based on datatype regardless of column? Alternatively, is there a way in SSIS 2012 to replace any null with a default value without a data conversion on each column?

For example, were I to have column “email” (varchar(255)) for customers where we had an email address. How can I then change each of those nulls to an empty string (‘’), but across hundreds of tables without specifying each one explicitly?

I have tried using a default value in the table definition, but it is still using NULL values since they are explicitly sent.

Tab Alleman Tab Alleman · Accepted Answer · 2016-04-27T13:23:02

Well I've learned something interesting and possibly useful to you.

I already knew that it was possible to insert a row of all DEFAULT values with this:

INSERT INTO dbo.DefTest DEFAULT VALUES;

Your question made me wonder if there were a similar way to UPDATE values to their DEFAULT value. So I tried this experiment:

CREATE TABLE dbo.DefTest (
   Id int NULL DEFAULT (0)
 , AStr varchar(15) NULL DEFAULT ('A')
 , BStr varchar(15) NULL DEFAULT ('B')
 , TF bit NULL DEFAULT (0)
 , Amount money NULL DEFAULT (100.00)
 );

INSERT INTO dbo.DefTest VALUES (1,'Test','Test',1,20);
INSERT INTO dbo.DefTest DEFAULT VALUES;
INSERT INTO dbo.DefTest VALUES  (NULL,NULL,NULL,NULL,NULL);

SELECT * FROM dbo.DefTest;

The SELECT produces exactly what you'd expect. A row with the specified values, a row with all default values, and a row with all NULL values.

Then I tried this:

 UPDATE dbo.DefTest
 SET Id = DEFAULT

The result was what I'd hoped: All rows had an Id value of 0, the default.

Now to set every column to its default value ONLY if it is currently NULL, I would need to use CASE statements, so I tried this:

 UPDATE dbo.DefTest 
 SET Id=CASE WHEN Id IS NULL THEN DEFAULT ELSE Id END
 , AStr = CASE WHEN AStr IS NULL THEN DEFAULT ELSE AStr END
 , BStr = CASE WHEN BStr IS NULL THEN DEFAULT ELSE BStr END
 , TF = CASE WHEN TF IS NULL THEN DEFAULT ELSE TF END
 , Amount = CASE WHEN Amount IS NULL THEN DEFAULT ELSE Amount END

But I got:

Incorrect syntax near the keyword 'DEFAULT'.

So I guess that UPDATE statements can only use DEFAULT as a direct assignment of column's value.

So to do what you want, you would simply need to run a UPDATE for every column that has a default value that you want to use to replace NULLs:

UPDATE MyTable
SET MyColumn = DEFAULT
WHERE MyColumn IS NULL

I have verified that this works.

You can save yourself the trouble of writing hundreds of statements by querying meta-views/tables, and generating dynamic sql statements and executing them.

Replacing Null values in SQL Server 2012 from SSIS Package

2 Answers