How to insert / change hierarchy field as text in SQL studio

Question

In SQL Server 2008, I have a hierarchyid field. How to change its value or insert a new row when using the "edit rows" command of SQL Server Management Studio? Is there a text representation to be converted to ID?

Yes, I know that I can do this with a query, but I would like to manually enter it as TEXT using the studio editor.

Thanks!

Answer 1

You can convert HIERARCHYID to string using:

hierarchyField.ToString(); 

You will get something like '/ 1 /', '/ 1/1 /', '/ 1/2 /', etc.

And you can convert such a string representation back to HIERARCHYID with

 SET hierarchyField = hierarchyid::Parse(string) 

or

 CAST('/2/' AS hierarchyid) 

Learn more about this at Technet.

Answer 2

You can enter the hierarchy identifier directly in the Visual Studio table as strings.

1.) For the root id, you simply enter a slash: /

2.) For one level down: / 1 / (must contain a trailing slash)

3.) For another sheet, the same level as step 2 above: / 2 /

4.) For a child from / 1 /: / 1/1 /

5.) The second child from / 1 /: / 1/2 /

etc .. Hope this helps.