Inserting child between descendants using HierarchyID datratype

• IT researcher

  SSCertifiable

  Points: 7567

  More actions

  March 26, 2013 at 1:10 am

  #270376

  Hi,

  We are using Sql server 2008 R2 express. We are trying to use HierarchyID datatype to represent organizational structure. We have to insert a child by using parent id. I found this query

  declare @root hierarchyid,@last_id hierarchyid,@id hierarchyid

  select @root=orgnode from [adventureworks].HumanResources.EmployeeDemo where employeeid=1

  SELECT @last_id = MAX(orgnode) FROM [adventureworks].HumanResources.EmployeeDemo WHERE orgnode.GetAncestor(1) = @root

  SELECT @id=@root.GetDescendant(@last_id, NULL)

  INSERT into [adventureworks].HumanResources.EmployeeDemo values(@id,12,'seh','Application Developer',1905-02-12);

  But this inserts a node at end in particular level and i sometimes want to insert node in between other nodes in that level.How can i achieve that?