Populate from other table if empty

Question

I have two tables, TableA and TableB. I want to populate TableC with values from TableA, and if it does not exist in TableA then populate from TableB for example

TableA

 ID    Value
 AA      1
 AC      1
 AE      1
 AF      1 

TableB

 ID    Value
 AB      1
 AD      1
 AE      1
 AF      1 

TableC

 ID    Value
 AA      1
 AB      1
 AC      1
 AD      1
 AE      1
 AF      1 

I tried using this query, but I only get results in TableA

 Select 
          isnull(nullif(a.ID,' '), b.ID) ID,
          cast(isnull(nullif(a.val,' '), b.val) as float) as Val

into TableC
from TableA a
FULL JOIN TableB b
ON a.ID = b.ID
         

Answer 1

I think the main problem is in your NULLIF calls, you're comparing a.id to a space, and a space is not null, so whether or not a.id is null, NULLIF returns something other than null, so the ISNULL call is not returning the value from b.

select
    case when a.id is null then b.id else a.id end as id,
    case when a.id is null then b.val else a.val end as val
from
    tableA a
    full join
    tableB b on b.id = a.id