Hierarchical database select / attach application (SQL Server)

Question

I recently came across a problem with selecting relationship data from 1 table and pasting into another table, 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

eg

This is the table in which I need to select and get all the relationship data. Since there can be an unlimited number of subheadings (is there a function I can create for this to create a loop?)

Then, as soon as I have all the data that I need to insert into another table, and now the identifier will have to change as the identifier should go in order (for example, I can not have id "2", for example, sub of 3) I hope that I can use the same function to select for insertion.

Answer 1

SQL Server 2005 , . :

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];

, (Where ParentID = 0), . ?

, , . , ?

!

Answer 2

Common Table Expression. SQL.

CTE .

Answer 3

, URL- Self-Joins SQL

Answer 4

sql. SQL , :

• SQL .
• node
• Nested Sets, , , node,

, , , . SQL, . , , , . - , .

MSSQL, , .