Hierarchical Database Select / Insert Statement (SQL Server)

Question

I have recently stumbled upon a problem with selecting relationship details from a 1 table and inserting into another table, i hope someone can help.

I have a table structure as follows:

ID (PK)   Name       ParentID<br>
1         Myname     0<br>
2         nametwo    1<br>
3         namethree  2

e.g

This is the table i need to select from and get all the relationship data. As there could be unlimited number of sub links (is there a function i can create for this to create the loop ?)

Then once i have all the data i need to insert into another table and the ID's will now have to change as the id's must go in order (e.g. i cannot have id "2" be a sub of 3 for example), i am hoping i can use the same function for selecting to do the inserting.

Answer 1

For the retrieval part, you can take a look at Common Table Expression. This feature can provide recursive operation using SQL.

For the insertion part, you can use the CTE above to regenerate the ID, and insert accordingly.

Answer 2

If you are using SQL Server 2005 or above, you may use recursive queries to get your information. Here is an example:

With tree (id, Name, ParentID, [level])
As (
    Select id, Name, ParentID, 1
    From [myTable]
    Where ParentID = 0

    Union All

    Select child.id
       ,child.Name
       ,child.ParentID
       ,parent.[level] + 1 As [level]
    From [myTable] As [child]
    Inner Join [tree] As [parent]
    On [child].ParentID = [parent].id)
Select * From [tree];

This query will return the row requested by the first portion (Where ParentID = 0) and all sub-rows recursively. Does this help you?

I'm not sure I understand what you want to have happen with your insert. Can you provide more information in terms of the expected result when you are done?

Good luck!

Answer 3

I hope this URL helps Self-Joins in SQL

Answer 4

This is the problem of finding the transitive closure of a graph in sql. SQL does not support this directly, which leaves you with three common strategies:

1. use a vendor specific SQL extension
2. store the Materialized Path from the root to the given node in each row
3. store the Nested Sets, that is the interval covered by the subtree rooted at a given node when nodes are labeled depth first

The first option is straightforward, and if you don't need database portability is probably the best. The second and third options have the advantage of being plain SQL, but require maintaining some de-normalized state. Updating a table that uses materialized paths is simple, but for fast queries your database must support indexes for prefix queries on string values. Nested sets avoid needing any string indexing features, but can require updating a lot of rows as you insert or remove nodes.

If you're fine with always using MSSQL, I'd use the vendor specific option Adrian mentioned.

Answer 5

hi Ash,

can you share your script? i need to write a script similar to yours but i have no idea how to do it..:(

Thanks bee