Conversion data type issue when UPDATE a NULL table

Question

I've got a query as below:

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL BEGIN
       drop table #Temp1
end

SELECT * into #Temp1
   from (
          SELECT 1, NULL, NULL, NULL, NULL
UNION ALL SELECT 2, NULL, NULL, NULL, NULL
UNION ALL SELECT 3, NULL, NULL, NULL, NULL
UNION ALL SELECT 4, NULL, NULL, NULL, NULL
UNION ALL SELECT 5, NULL, NULL, NULL, NULL
UNION ALL SELECT 6, NULL, NULL, NULL, NULL
UNION ALL SELECT 7, NULL, NULL, NULL, NULL
UNION ALL SELECT 8, NULL, NULL, NULL, NULL
UNION ALL SELECT 9, NULL, NULL, NULL, NULL
)
as databases (tempid, tasknr, devcat, taskop, taskcl)

IF OBJECT_ID('tempdb..#Temp1a') IS NOT NULL BEGIN
       drop table #Temp1a
end

SELECT * into #Temp1a
   from (
select
cast(ROW_NUMBER() OVER(PARTITION BY parent_change ORDER BY number)as int) as tempid
,number as tasknr
,category as cat
,orig_date_entered as taskop
,case when (CM3TM1.status = 'closed') then CM3TM1.date_entered else NULL end as taskcl
FROM CM3TM1
WHERE parent_change IN ('C45168')
and category = 'NEU Development' 
)
as databases (tempid, tasknr, devcat, taskop, taskcl)


UPDATE
    #Temp1
SET
    #Temp1.tasknr = #Temp1a.tasknr,
    #Temp1.devcat = #Temp1a.devcat
FROM
    #Temp1a
INNER JOIN
    #Temp1
ON
    #Temp1.tempid = #Temp1a.tempid

and I'm getting

Msg 245, Level 16, State 1, Line 38 Conversion failed when converting the varchar value 'T85158' to data type int.

T85158 is related to 'tasknr' column.

I need to update such NULL table with different types of data where number of rows is changeable. I had tried to add ,cast((number) as int) as tasknr but it didn't help. Please let me know how to make this work correctly.

Answer 1

Reason for the error is correctly put by Damien. Below is just another flavor of the possible fix. Modify your upper part of script to this:

SELECT * into #Temp1
   from (
          SELECT 1 tempid, cast (NULL as varchar(19)) tasknr , cast(NULL as decimal(13,4)) devcat, cast(NULL as int) taskop, cast(NULL as xml) taskcl
UNION ALL SELECT 2, NULL, NULL, NULL, NULL
UNION ALL SELECT 3, NULL, NULL, NULL, NULL
UNION ALL SELECT 4, NULL, NULL, NULL, NULL
UNION ALL SELECT 5, NULL, NULL, NULL, NULL
UNION ALL SELECT 6, NULL, NULL, NULL, NULL
UNION ALL SELECT 7, NULL, NULL, NULL, NULL
UNION ALL SELECT 8, NULL, NULL, NULL, NULL
UNION ALL SELECT 9, NULL, NULL, NULL, NULL
)

.......

Edit: To DELETE those rows which have every column as NULL, use this script:

DELETE FROM #temp1 WHERE
COALESCE(tasknr,devcat,taskop,taskcl) IS NULL