Q:

Sort/Merge without cursor

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.

A:

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;

