CODEWITHSUNDEEP
sql-server
Yuri Astrakhan
Yuri Astrakhan

Reputation: 9965

How to insert/edit hierarchyid field as text in SQL studio

In SQL server 2008 I have a hierarchical field (hierarchyid). How do I change its value or insert new row when using SQL Server Management Studio "edit rows" command? Is there a textual representation that will be converted to an ID?

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

Thanks!

Upvotes: 8

Views: 8406

Answers (2)

Jason
Jason

Reputation: 416

You can enter the hierarchyID directly into the Visual Studio table as strings.

1.) For the root ID you just type a forward slash: /

2.) For one level down: /1/ (must include the trailing slash)

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

4.) For the child of /1/: /1/1/

5.) Second child of /1/: /1/2/

etc. Hope this helps.

Upvotes: 1

marc_s
marc_s

Reputation: 754598

You can convert a HIERARCHYID to a string using:

hierarchyField.ToString();

You'll get something like '/1/', '/1/1/', '/1/2/' and so forth.

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

SET hierarchyField = hierarchyid::Parse(string)

or

CAST('/2/' AS hierarchyid)

More details on this can be found on Technet

Upvotes: 13

Related Questions