父子节点

create table Jiedian(

node hierarchyid primary key,

nodLevel as node.GetLevel(),

id int unique not null,name varchar(10) not null,

info varchar(20) not null

)

--插入根节点

insert Jiedian values(hierarchyid::GetRoot(),101,'Sam','CEO')

select node as '16' ,node.ToString() as string ,id,name,info

from hierarchyTbl

--插入一级第一个子节点

declare @paren hierarchyid

select @paren=cast('/'as hierarchyid)

insert hierarchyTbl values(@paren.GetDescendant(null,null),102,'Sandy','Manager')

select node as '16' ,node.ToString() as string ,id,name,info

from hierarchyTbl

--/2/

declare @parent hierarchyid

select @parent=Cast('/' as hierarchyid)

declare @Child hierarchyid

select @Child=Cast('/1/' as hierarchyid)

insert Jiedian values(@p.GetDescendant(@child,null),103,'Jack','Manager')

--/1/1/--->/1/

declare @GrandChild hierarchyid

select @GrandChild=cast('/1/' as hierarchyid)

insert Jiedian values(@r.GetDescendant(null,null),104,'Mary','Team')

--/1/2/

declare @Fa hierarchyid

select @Fa =cast('/1/' as hierarchyid)

declare @Son hierarchyid

select @Son=cast('/1/1/' as hierarchyid)

insert Jiedian values

(@u.GetDescendant(@b1,null),105,'Nancy','team')