Issue of Select into Temp table with NULL fields

Sometimes when we create Temporary tables we might need to create some null fields. Idea is to update them later.

Consider this script which seems correct.

SELECT 'JOHN' as Name, NULL as Company into #temp

Now we will try to populate the null field with some data, probably with char data.

UPDATE #temp
SET Company = 'ABC and Company'

We are getting this error.

If you check the error message carefully, you might notice it’s a converting error. Actual problem is null field is considered to be an int, unless it’s properly defined with a type.

So correct statement to create the temp table is like this;

SELECT 'JOHN' as Name, Cast(null as varchar(50)) as Company into #temp