Search code examples
sql-server-2008temp-tables

Conversion error with NULL column and SELECT INTO

I'm experimenting with temporary tables and running into a problem.

Here's some super-simplified code of what I'm trying to accomplish:

IF(Object_ID('tempdb..#TempTroubleTable') IS NOT NULL) DROP TABLE #TempTroubleTable

select 'Hello' as Greeting,
    NULL as Name
into #TempTroubleTable

update #TempTroubleTable
set Name = 'Monkey'
WHERE Greeting = 'Hello'


select * from #TempTroubleTable

Upon attempting the update statement, I get the error:

Conversion failed when converting the varchar value 'Monkey' to data type int.

I can understand why the temp table might not expect me to fill that column with varchars, but why does it assume int? Is there a way I can prime the column to expect varchar(max) but still initialize it with NULLs?

Solution

  • You need to cast null to the datatype because by default its an int

    Select 'hello' as greeting,
            Cast (null as varchar (32)) as name
Into #temp