SQL Split - insert into a hierarchical table structure

Question

I use the Split function (found on social.msdn.com) and when executed manually in the query window

SELECT * FROM dbo.Split('/ABC/DEF/GHI/JKL', '/')  

I get the following

Id  Name
--  ----
 1
 2   ABC
 3   DEF
 4   GHI
 5   JKL

Where Id is just a serial number indicating the position in the source string, and Name is the name of the node. No hierarchical information.

Now, the next step is to put this in a hierarchical data structure in the database. I am trying to do this in a stored procedure and with my SQL skills what they are, I hit a wall. Here is what I would like to have: (note that the Id column above is not associated with the Id or ParentId column here.)

Id  ParentId  Name  FullName
--  --------  ----  --------
1     NULL     ABC   /ABC
2      1       DEF   /ABC/DEF
3      2       GHI   /ABC/DEF/GHI
4      3       JKL   /ABC/DEF/GHI/JKL

SP ( GetId @FullName). GetId , node. node , Id , - , , node :

DECLARE @count int

-- // is there already a row for this node?
SELECT @count = COUNT(CatId)
FROM Category
WHERE FullName = @FullName

-- // if no row for this node, create the row
-- // and perhaps create multiple rows in hierarchy up to root
IF (@count = 0)
BEGIN
    SELECT * FROM Split(@FullName, '/')
    -- // NOW WHAT ???
    -- // need to insert row (and perhaps parents up to root)
END

-- // at this point, there should be a row for this node
-- // return the Id associated with this node
SELECT Id
FROM Category
WHERE FullName = @FullName

( ), , .

CREATE TABLE Category (
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ParentId int NULL,
    Name nvarchar(255) NOT NULL,
    FullName nvarchar(255) NOT NULL)

Id Category ParentId node.

, /ABC/DEF/GHI/JKL ''/ABC/DEF/XYZ/LMN/OPQ ', SELECT * FROM Category, :

Id  ParentId  Name  FullName
--  --------  ----  --------
1     NULL     ABC   /ABC
2      1       DEF   /ABC/DEF
3      2       GHI   /ABC/DEF/GHI
4      3       JKL   /ABC/DEF/GHI
5      2       XYZ   /ABC/DEF/XYZ
6      5       LMN   /ABC/DEF/XYZ/LMN
7      6       OPQ   /ABC/DEF/XYZ/LMN/OPQ

Q: , node, < W632 > ? - :

GetId(@FullName)
{
If Category exists with @FullName
    return CatId
Else  // row doesn't exist for this node
    Split @FullName, order by Id DESC so we get the leaf node first
    Create Category row
      @FullName,
      @Name,
      @ParentId = Id of next FullName (call GetId with FullName of next row from Split)
}

Answer 1

CTE , RowNumbering

With TMP AS (
    SELECT Id, Data as Name, RN=ROW_NUMBER() over (Order by Id ASC)
    FROM dbo.Split('/ABC/DEF/GHI/JKL', '/')
    where Data > ''
), TMP2 AS (
    SELECT TOP 1 RN, CONVERT(bigint, null) ParentId, Name, convert(nvarchar(max),'/' + Name) FullName
    From TMP
    Order by RN
    union all
    SELECT n.RN, t.RN, n.Name, t.FullName + '/' + n.Name
    from TMP2 t
    inner join TMP n on n.RN = t.RN+1)
select *
from tmp2
order by RN

, ID = 1

IF (@count = 0)
BEGIN
    With TMP AS (
        SELECT Id, Data as Name, RN=ROW_NUMBER() over (Order by Id ASC)
        FROM dbo.Split('/ABC/DEF/GHI/JKL', '/')
        where Data > ''
    ), TMP2 AS (
        SELECT TOP 1 RN, CONVERT(bigint, null) ParentId, Name, convert(nvarchar(max),'/' + Name) FullName
        From TMP
        Order by RN
        union all
        SELECT n.RN, t.RN, n.Name, t.FullName + '/' + n.Name
        from TMP2 t
        inner join TMP n on n.RN = t.RN+1)
    insert Category(CatId, ParentId, Name, FullName)  --<< list correct column names
    select RN, ParentId, Name, FullName
    from tmp2
    order by RN
END