SQL Split - insert into hierarchical table structure

I'm using a Split function (found on social.msdn.com) and when executing manually in a 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 sequential number indicating position within the original string and N开发者_如何学JAVAame is the name of that node. No hierarchical information yet.

Now, the next step is to put this into a hierarchical data structure in the DB. I'm trying to do this in a stored proc and with my SQL skills being what they are, I've hit a wall. Here's what I'd like to have: (NOTE that the Id column above is not related to the Id or the 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

I've got this far with my SP (called GetId with param @FullName) - GetId should return the Id associated with this node. If the node doesn't exist, it should be created and the Id from that new row should be returned - in other words, the consumer of this SP shouldn't care or know if the node exists prior to its calling it:

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

The Category table (adjacency list) where these items will eventually end up, through a series of inserts, has the following structure.

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)

As result, I do not want to generate a value for the Id column in the Category table and need to get the appropriate ParentId for each node.

After the paths '/ABC/DEF/GHI/JKL' and '/ABC/DEF/XYZ/LMN/OPQ' were processed and I did a SELECT * FROM Category, I would expect to see the following:

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: would it be possible to call back into the SP recursively starting at the outer most node, until the node existed or we were at the ultimate parent? Something to the effect of:

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)
}

---

You can use CTE to achieve this, in combination with 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

Now for the 2nd part, this inserts the entire hierarchy, but starts with 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