Link to home
Start Free TrialLog in
Avatar of tesupport
tesupportFlag for United States of America

asked on

How to create table with hierarchical records in SQL Server 2012

I have a table with 4 fields, ID, ECFolder, Classorder and EventClass.  

Sample records would look like this:
ID    ClassOrder   ECFolder    EventClass
10          1               EC1              Correspondence
10          2               EC2              Emails

What this represents is a document structure of Correspondence\Emails for document (ID field) 10.  The main folder always has a class of 1 and subsequent subfolders are > 1.

I need to put insert these records into a table showing that Correspondence is the parent of the subfolder Emails
The table this info goes into has 3 columns, ECFolder, EventClass, ParentID.  The final table would look like this, with the ECFolder.ParentID holding the identifier for the subfolder.

ECFolder   EventClass                ParentID
EC1            Correspondence      EC2
EC2            Emails                        NULL

I have over 5 million records to insert.  The number of subfolders for each document ranges from 0 to 10.  The next document structure to be processed could be something like this:

ID    ClassOrder   ECFolder    EventClass
11          1               EC3              Correspondence
11          2               EC4              Emails
11          3               EC5              Discovery

and once inserted into the new table would look like this:
ECFolder   EventClass                ParentID
EC3            Correspondence      EC4
EC4            Emails                        EC5
EC5            Discovery                  NULL
 
Any help in devising a SQL procedure to accomplish this would be appreciated.
Avatar of arnold
arnold
Flag of United States of America image

first, anything referenced as ID should be unique, and should be numeric, integer.

you can have directory tables, document tables such that document tables includes the document and the folder it is in.
The directory table will have the directories and the directory it is in
with drive letter as the top
c:\
c:\program files
c:\datafiles
c:\datafiles\someotherfile\somedocument.doc
Directories table
ID description parentdir
0 c:\                   null
1 d:\                  null
2 program files  0
3 datafiles          0
4 someotherfile 3

Document table
ID documentName directoryID
0 somedocument.doc 4
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Phillip Burton
Phillip Burton

Depends what functionality you want out of it.

For functions such as finding descendants and parents etc., you could use HIERARCHYID - see https://msdn.microsoft.com/en-us/library/bb677290.aspx for details.
tesupport, do you still need help with this question?