(PatternID, PatternString)
1, “1, 2, 18, 4”
2, “1, 2, 54, 60, 4”
3, “1, 3, 19, 4”
4, “1, 3, 65, 4”

(NodeID, ParentNodeID, CommandID, PatternID)
 1, Null,  1, Null
 2,    1,  2, Null
 3,    2, 18, Null
 4,    3,  4, 1
 5,    2, 54, Null
 6,    5, 60, Null
 7,    6,  4, 2
 8,    1,  3, Null
 9,    8, 19, Null
10,    9,  4, 4
11,    8, 65, Null
12,   11,  4, 3

/* This procedure loads the NodeList table 
 *  by reading from the BehaviorPattern table.
 *
 *  For each Behavior Pattern, the PatternString is dissected 
 *  by delimiters between each CommandID within the pattern.
 *  
 *  Each CommandID is looked up in the NodeList table, and if
 *  it doesn't exist below the appropriate parent node, it is created.
 */

declare @PatID    integer
declare @PatType  Integer
declare @CmdID    INTEGER
DECLARE @ParentID INTEGER
DECLARE @TempID   INTEGER
declare @ptrDelim INTEGER
DECLARE @First    INTEGER
DECLARE @Last     INTEGER
DECLARE @CNT      INTEGER
Declare @Pat      varchar(250)

set nocount on
DECLARE Pat_Cursor CURSOR FOR 
SELECT BehaviorPattern.PatternID, BehaviorPattern.PatternType, 
    BehaviorPattern.PatternString
FROM BehaviorPattern WITH (nolock) LEFT OUTER JOIN
    NodeList WITH (nolock) ON 
    BehaviorPattern.PatternID = NodeList.PatternID
WHERE (NodeList.PatternID IS NULL) AND 
    PatternString NOT LIKE '%NULL%'

OPEN Pat_Cursor
FETCH NEXT FROM Pat_Cursor INTO @PatID, @PatType, @Pat
WHILE @@FETCH_STATUS = 0
BEGIN 
  SELECT @First = 1
  SELECT @Last  = 0
  -- Dissect @Pat by delimiter ', ' and loop through each numeric value.
  --PRINT 'Pattern=' + cast(@PatID as varchar(20)) + '; String="' + @Pat + '"'
  SELECT @ParentID = NULL
  WHILE LEN(@Pat)>0
  BEGIN
    SELECT @PtrDelim = CHARINDEX(', ',@Pat,1) --point to the first delimiter
    IF @PtrDelim = 0 
    BEGIN -- Delimiter not found - must be the last one in the string
      SELECT @Last=1
      SELECT @CmdID = CAST(ISNULL(@Pat,'0') as INTEGER)
    END
    ELSE
    BEGIN -- Delimiter found - parse out the CmdID
      SELECT @First=0
      SELECT @CmdID = CAST(ISNULL(SUBSTRING(ISNULL(@Pat,""),1,@ptrDelim - 1),'0') as INTEGER)  
    END
   /*
   For each number within a pattern string,
   create a node if it doesn't exist within NodeList
   below the appropriate parent node.
   */
    IF @ParentID is null
    BEGIN
      --@TempID stores the NODE of the CommandID so it can be used as a ParentID next time thru
      --@CNT stores a zero if there is not an existing node with the appropriate parent
      SELECT @TempID=NODE FROM NodeList   with (nolock) WHERE (ParentNode IS NULL) AND (CommandID = @CmdID)
      SELECT @CNT=@@ROWCOUNT -- 0 if we didn't find the node, 1 if we did
    END
    ELSE    
    BEGIN
      --@TempID stores the NODE of the CommandID so it can be used as a ParentID next time thru
      --@CNT stores a zero if there is not an existing node with the appropriate parent
      SELECT @TempID=NODE FROM NodeList  with (nolock) WHERE (ParentNode = @ParentID) AND (CommandID = @CmdID)
      SELECT @CNT=@@ROWCOUNT -- 0 if we didn't find the node, 1 if we did
    END
    IF @CNT = 0
    BEGIN
      --Create the node if it does not exist below the appropriate parent
      INSERT INTO Nodelist  with (rowlock) (ParentNode, CommandID, PatternID)
                    VALUES(@ParentID , @CmdID   , @PatID)

      IF @@ERROR<>0 
      BEGIN 
        PRINT 'Could not insert Node: ' 
        + 'ParentNode='  + cast(isnull(@ParentID,'NULL') AS varchar(20))
        + ', CommandID=' + cast(isnull(@CmdID   ,'NULL') AS varchar(20))
        + ', PatternID=' + cast(isnull(@PatID   ,'NULL') AS varchar(20))
      END
      SELECT @TempID=@@IDENTITY
    END --IF @CNT=0

    SELECT @ParentID = @TempID
    IF @Last=0
    BEGIN
      -- Chop off the left side of @PAT each time through the loop:
      SELECT @PAT = SUBSTRING(@Pat,@ptrDelim + 2,LEN(@Pat)-(@ptrDelim+1))
    END
    ELSE
    BEGIN
      SELECT @PAT = ""
    END
  END -- While LEN(@Pat)>0

  FETCH NEXT FROM Pat_Cursor INTO @PatID, @PatType, @Pat
END --FETCH LOOP

CLOSE Pat_CURSOR
DEALLOCATE Pat_CURSOR

declare @c int
set nocount on 
DROP TABLE _NL

CREATE TABLE _NL (nodeId    INT IDENTITY(1,1) NOT NULL 
                , pNodeId   INT                   NULL
                , CommandId INT                   NULL
                , PatternId INT                   NULL
                , Parentage VARCHAR(250)          NULL
                  )

DROP TABLE _BP
CREATE TABLE _BP (
                  PatternId   INT          NOT NULL 
                , Processed   VARCHAR(250)     NULL 
                , Unprocessed VARCHAR(250)     NULL
                 )
INSERT INTO _BP (
      PatternID
    , Unprocessed
    , Processed
      )
SELECT                    TOP 500
       PatternID
     , PatternString
     , NULL 
FROM BehaviorPattern WITH (NOLOCK)
WHERE PatternString NOT LIKE '%NULL%'
ORDER BY PatternID

select @C = 0
-- SELECT * FROM _BP

WHILE EXISTS (SELECT * FROM _BP WHERE Unprocessed LIKE '%,%')
BEGIN

  select @C = @C + 1
  print 'Loop number ' + cast(@C as varchar)

  INSERT INTO _NL 
  SELECT 
    DISTINCT 
      pNodeId = (SELECT nodeId FROM _NL NL 
                 WHERE isnull(BP.Processed,0) = isnull(NL.Parentage,0))
    , CommandId = LEFT(BP.Unprocessed, CHARINDEX(',',BP.Unprocessed)-1)
    , PatternId = NULL
    , Parentage = BP.Processed
    FROM _BP BP
    WHERE Unprocessed LIKE '%,%'

  UPDATE _BP
    SET
    Unprocessed = right(Unprocessed,len(Unprocessed)-charIndex(',',Unprocessed))
  , Processed = isnull(Processed + ',','') + left(Unprocessed,charIndex(',',Unprocessed)-1) 
    From _BP BP WITH (NOLOCK)
    Where Unprocessed like '%,%'

End
print 'end of loop'

Insert Into _NL 
  Select 
  Distinct 
  pNodeId = (Select nodeId From _NL NL Where BP.Processed = NL.Parentage)
  , CommandId = Unprocessed
  , PatternId
  , Parentage = BP.Processed
  From _BP BP WITH (NOLOCK)