Il existe pas mal d’articles sur le nouveau type CLR HierarchyID. Voici quelques pointeurs d’ailleurs :

Par contre, il y a peu d’informations sur la façon de faire pour migrer une table contenant par exemple un Identifiant et son parent (EmployeeID et ManagerID) vers une table supportant un type HierarchyID.

J’ai trouvé quelques essais, qui se sont trouvés être … faux (c’est balot !!)

Le principe finalement, c’est de “construire” Le hierarchyID à partir des données de la table d’origine.

On va éviter de passer par des curseurs, trop lent, et on va utiliser 2 choses:

  1. Le Row_Number() qui va nous être utile pour numéroter chaque filleul d’un manager : Supposons que j’ai un manager avec 4 filleuls, je veux que chaque filleul soit noté de 1 à 4
  2. Deux CTE : la première va nous permettre de faire le calcul précédent. La deuxième va créer le hierarchyID pour chaque employé (recursivement) grace aux informations de la première Smile

Du coup on est capable de générer un hierarchyID (correct cette fois ci Smile with tongue out) avec … une seule requete :

   1: USE AdventureWorks

   2:  

   3: -- Construction d'une table temporarire permettant de 

   4: -- connaitre le numéro de chaque enfant d'un manager

   5: -- Pour un managerID = 3, on voit qu'il y a 7 enfants par exemple, 

   6: -- numéroté de 1 à 7

   7: WITH employees (EmployeeID, ManagerID, Num) AS

   8: (

   9:     SELECT 

  10:         EmployeeID, 

  11:         ManagerID,

  12:         ROW_NUMBER() OVER 

  13:             (PARTITION BY ManagerID ORDER BY ManagerID) 

  14:     FROM HumanResources.Employee

  15: ), 

  16: hierarchies(orgnode, EmployeeID) 

  17: AS (

  18:     -- Récupération des managers de plus haut 

  19:     -- niveau (managerId == null)

  20:     SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID 

  21:     FROM employees AS C 

  22:     WHERE ManagerID IS NULL 

  23:     UNION ALL 

  24:     -- Récursivité sur l'ensemble des filleuls

  25:     SELECT 

  26:     -- Construction du hierarchyId

  27:     -- On est au niveau inférieur, donc :

  28:     -- Concaténation du orgnode du parent 

  29:     -- + numéro de l'enfant actuel

  30:     CAST(p.orgnode.ToString() + 

  31:     CAST(C.Num AS varchar(30)) + '/' AS hierarchyid), 

  32:     C.EmployeeID

  33:     FROM employees AS C 

  34:     JOIN hierarchies AS p ON C.ManagerID = P.EmployeeID 

  35: )

  36: -- Le select pour vérifier le résultat

  37: -- qui pourrait servir à faire un insert ou update

  38: -- de la table de destination

  39: SELECT orgnode.ToString(), * FROM hierarchies