Adding new rows (Value, NULL) to a multiple parent child hierarchy-CodePudding

I have a multiple parent child hierarchy consisting of 100k records. This is a small subset.

Child	Parent
1	2
1	3
2	3

I need to go through each value from the column Parent and check if the same value also exists in the column Child. If it does not, then create a row with (value, NULL). In this example the value 3 does not exist in the Child column so I need it to create a new row with the values (3, NULL).

Child	Parent
3	NULL
1	2
1	3
2	3

My code doesn't return an error but it doesn't do what I want it to do.

INSERT INTO #table (child, parent)
SELECT (CASE WHEN parent not in (Select child from #table) THEN parent END) as child, Null
FROM #table

I get many new rows with (NULL,NULL) but 0 rows with (value, NULL). It must somehow always skip ahead to the else clause when there are so many new rows with (NULL, NULL) but not sure why.

Using SQL Server Management Studio 17. Any help is greatly appreciated.

CodePudding user response：

You shouldn't use case here, but a self left join instead:

INSERT INTO #table (child, parent)
SELECT DISTINCT t1.parent, NULL
FROM #table t1
LEFT JOIN #table t2
    ON t1.parent = t2.child
WHERE t2.child IS NULL

See live demo on Db<>Fiddle