Add product in hierarchy manner in a table

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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 7-Jan-18 23:34pm

Telstra

Updated 9-Jan-18 19:51pm

Add a Solution

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 :)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

W∴ Balboos, *GHB* · Answer 1 · 2018-01-09T01:05:00

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.

Maciej Los · Answer 2 · 2018-01-09T07:22:00

Nilesh Avhad - Professional Profile[^] wrote:
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[^]

Telstra · Answer 3 · 2018-01-09T19:51:00

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.

Add product in hierarchy manner in a table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0