SQL Insert into select and removal of Null values

Question

I have to populate one table with values from a different one, so I'm using:

insert into A (pk1, pk2)
select pk1, pk2
from B

The complication is that for each record in table B where one of the values is Null I want to create 3 records in table A with some given values. Since the number of columns is 2 then I will want 3 x 3 = 9 records in A e.g.

insert into A (pk1, pk2)
select isNull(pk1, 'value1' or 'value2' or 'value3'),
       isNull(pk2, 'value4' or 'value5' or 'value6')
from B

The syntax above is clearly incorrect, but just to give you an idea of what I'm trying to do. Since I'm not an expert the temptation is to do it using a procedural programming language, but I bet there is a way to do it in SQL. I'm using MS SQL Server if that makes any difference.

Answer 1

You can create a table valued function that returns 3 rows if the parameter is null

create function dbo.ExpandIfNull (@value int)
returns table
as
return 
(
    select Value
    from (values (1),(2),(3)) as v(Value)
    where @value is null

    union all

    select @value
    where @value is not null
)

Then use cross apply in your select statement:

select val1.Value, val2.Value
from B
cross apply dbo.ExpandIfNull(pk1) val1
cross apply dbo.ExpandIfNull(pk2) val2