title: SQL HierarchyID data type , how to generate OrgNode for fast inserting
body: <p>I’m thinking about changing a parent child hierarchy table to hierarchyid datatype,
  I am looking for  T-sql querty (I guess it will be implement with  CTE) that will help me to generate new nodes and insert them to the hierarchyid table
  I have seven fixed level that I can run one by one and find for each level all of his new nodes.
  Now, the problem is that I need it to be inserted without using inline function or cursor, due to large amount of member ( about 1Mil at the low level).
  I have a query that return all the new node that need to be inserting with their parents
  Mean: I have list of @parentOrgNode hierarchyID ,@NewCustomerCode varchar(255)
  For example : list of new node (level 4) that need to be insert </p>
  
  <pre><code>select distinct  C1.orgNode as ParentCode,S.PH1_LVL4_NAME,S.PH1_LVL4_CODE
  from [Staging].[dbo].[CustomerHierarchy] S
  inner join customer C1 on C1.CustomerCode=PH1_LVL3_CODE
  left outer join Customer C2 on C2.CustomerCode=PH1_LVL4_CODE
  where C2.CustomerCode is null
  </code></pre>
  
  <p>For each return row need to generate OrgNode  for inserting to Customer table
  Thanks</p>