Click here to Skip to main content
15,877,966 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have a table whose one columns is of type hierarchyid. Now I want to add one more product and sub products to that new product. Could you please help to how to get the last id of existing product and add child product to it.

Structure of that column is like /1/,/1/1/,/1/1/1/ and so on. In this way we have /7/,/7/1/..... Now I want to add new one like /8/, /8/1/,/8/1/1 and so on.

What I have tried:

I have added the new record using below query. But the product is getting added on first row on UI. It should be get added on last row. TaxonomyNode is a hirarchyid column. taxonomyid is identity column.

insert into tblname
(TaxonomyId,TaxonomyNode,TaxonomyName,DeletedInd,CreatedOn,CreatedBy,UpdatedOn,UpdatedBy)

values ('/8/','test','0',getdate(),'abd',getdate(),'abd')
Posted
Updated 9-Jan-18 19:51pm
Comments
Kornfeld Eliyahu Peter 8-Jan-18 6:28am    
Table is a table - not meant to display hierarchical data but tabular data...
Use hierarchical UI element like treeview to display hierarchical data...
Telstra 8-Jan-18 6:35am    
But table is created like this and I have to follow the existing structure.
Maciej Los 8-Jan-18 7:14am    
You mean, Taxonomyid is like: /8/, /8/1/,/8/1/1?
Do you want to find C# or clear T-SQL solution? Which one you prefer?
Telstra 8-Jan-18 7:53am    
Tsql solution is the requirements.
Sinisa Hajnal 8-Jan-18 10:19am    
This should be refactored, not used :)

There are two basic ways to create a hierarchical relationship in your tables.

1) A single table, a parent entry and each further (child entry) has a column to associate it to the parent entry (the parent's child should be itself). This could go on, ad nauseamcreating a tree of any depth. This is a dumb and inefficient design - but it can be made to work. There are situations where this is "the" solution, but, it is better to . . .
2) Create a second table - have a column referencing the ID of the first (parent) table and everything with the same reference are children of the same parent. You can hang as many table off of the parent as you wish, and even hang children on the children. It's flexible and capable of a great many types of changes that have no effect on existing code.

Since SQL, MySQL, &etc are "Referential" databases, using (2), a referential methodology, would be best at taking care of your problems. Note that you can use the existing table as parent, create your child table, and everything that used the original parent won't even be aware of it unless you make them aware.
 
Share this answer
 
I have added the new record using below query. But the product is getting added on first row on UI. It should be get added on last row. TaxonomyNode is a hirarchyid column. taxonomyid is identity column.


There's no such of expression: "first row" or "last row" in a terminology of relational database[^]. An order of records depends on many factors, for example: index, primary and foreign keys, etc. An order of data in UI depends of application's settings.

If you want to sort data in a specific order, especially when you want to display them, you have to use ORDER BY clause[^].

For further details, please read this:
Hierarchical Data (SQL Server) | Microsoft Docs[^]
Hierarchical Data (SQL Server)[^]
Tutorial: Using the hierarchyid Data Type | Microsoft Docs[^]
and also:
sql - How does database indexing work? - Stack Overflow[^]
 
Share this answer
 
v3
Hi,

I have found the solution. We do not need to use such a functions like GetAncestor or others. We can insert the record directly in hierarchyid columns like
SQL
insert into tblname values ('/5/23/1/','prod name','0',getdate(),'abd',getdate(),'abd')


I have implemented this way and its working.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900