Cloning tree structure with guid

I am trying to clone tree structure to another table, but the problem is original table has guid as ParentId and Id and new table or table that I want to clone has int as ParentId and Id. I am trying to find the solution so any help is welcomed I tried next as a solution but unfortunately does not work

WITH tree (id, parentid, level, name, parentIdInt, chiledId) as 
(
   SELECT id, parentId, 0 as level, name, 1 as parentIdInt, 0 as chiledId
   FROM tableA a
   WHERE a.parentId IS NULL  
   UNION ALL

   SELECT b.id, b.parentId, tree.level + 1, b.name, tree.parentIdInt, 
          cast((tree.chiledId + 1 + tree.parentIdInt) as int)
   FROM tableA b 
   INNER JOIN tree ON tree.id = b.parentId
)

SELECT *
FROM tree

If it is impossible to do it with a single query then any other solution will be acceptable expect with cursor 🙂 due to a larger amount of data

Here is the Sample so the output of a query(SP) should be tree structure with int-s as Id, ParentId

Answer

How about this:

;with cte as 
(
    select row_number() over(order by id) rn, *
    from tableA
)

select c1.rn id, c2.rn parentid, c1.name
from cte c1
left join cte c2 on c1.parentid = c2.id

or you can simply use checksum() function:

select checksum(id) id, checksum(parentid) parentid, name
from tableA

Please note, with the second approach you might get collisions.

Advertisement

Answer