Sort/Merge without cursor

Question

Without using a cursor, I am trying to come up with T-SQL code that would accomplish the following:

On the following table,

SourceData

that has two columns, [ColA] and [ColB], where both are nvarchar(255), with the following example data:

ColA    |  ColB
==================
AAA     |  TripleA
TripleA |  AAA
AAA     |  ThreeAs
ThreeAs |  AAA
BBB     |  TripleB
TripleB |  BBB
BBB     |  ThreeBs
ThreeBs |  BBB

etc.,

extract the row data into TWO tables,

TableA_Root, and TableB_Children

Where TableA_Root has these columns: [ROID identity], [Root]

and TableB_Children has these columns: [COID identity],[fKey_ROID],[Child]

Such that the resulting table has the example data as such:

TableA_Root
==============
1  |  AAA
2  |  BBB

TableB_Children
===============
1 | 1 | TripleA
2 | 1 | ThreeAs
3 | 2 | TripleB
4 | 2 | ThreeBs

At first, I thought I would use a Cursor. But, that is not the optimal approach, I am sure. Obviously, this is a sort and merge, which I could do outside of SQL. Some of the ideas I have tried with subqueries using "IN" or "EXISTS" but my attempts are falling short. I could use a fresh perspective.

Answer 1

Assuming there is a primary key on SourceData where you can't have duplicates of the same row then this would get you what you want...

With    cte1 As
(
        Select  Row_Number() Over (Order By ph) As ph, 
                ColA, 
                ColB
        From   (Select  1 As ph, 
                        ColA,
                        ColB
                From    SourceData) As n
)
Insert  TableA_Root (ROID, Root)
Select  Row_Number() Over (Order By c.ph) As ROID,
        ColA
From    cte1 c
Where   Not Exists (Select  1
                    From    cte1 c2
                    Where   c.ColA = c2.ColB
                    And     c.ph > c2.ph);

Insert  TableB_Children (COID, ROID, Child)
Select  Row_Number() Over (Order By ta.ROID), 
        ta.ROID, 
        tb.Colb
From    TableA_Root ta
Join    SourceData tb
        On  ta.Root = tb.ColA;