tesupport
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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
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