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.
tesupportAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Can you describe you context a little bit more?

Your result will produce overlapping sets when ECFolders per ID are not distinct sets.

E.g.

 
DECLARE @Source TABLE
	(
		ID INT,
		ClassOrder INT,
		ECFolder NVARCHAR(255),
		EventClass NVARCHAR(255)
	);

INSERT INTO @Source 
VALUES	( 10, 1, 'EC1', 'Correspondence' ),
	( 10, 2, 'EC2', 'Emails' ),
	( 11, 1, 'EC3', 'Correspondence' ),
	( 11, 2, 'EC4', 'Emails' ),
	( 11, 3, 'EC5', 'Discovery' );

DECLARE @Result TABLE 
	(
		ECFolder NVARCHAR(255),
		ParentECFolder NVARCHAR(255),
		EventClass NVARCHAR(255)
	);

-- The entire recursive CTE.
WITH Hierarchy AS 
	(
		SELECT	A.ID,
			A.ClassOrder,
			A.ECFolder,
			A.EventClass,
			CAST(NULL AS NVARCHAR(255)) AS ParentECFolder,
			0 AS [Level],
			'\\' + CAST(A.ID AS NVARCHAR(MAX)) + '\' + CAST(A.EventClass AS NVARCHAR(MAX)) AS [Path]
		FROM	@Source A
		WHERE	A.ClassOrder = 1
		UNION ALL
		SELECT	C.ID,
			C.ClassOrder,
			C.ECFolder,
			C.EventClass,
			P.ECFolder,
			P.[Level] + 1,
			P.[Path] + '\' + CAST(C.EventClass AS NVARCHAR(MAX))
		FROM	Hierarchy P
			INNER JOIN @Source C ON C.ID = P.ID	AND	C.ClassOrder = P.ClassOrder + 1
	)
	SELECT	*
	FROM	Hierarchy H
	ORDER BY H.[Path];

-- Your desired result.
WITH Hierarchy AS 
	(
		SELECT	P.ID,
			P.ClassOrder,
			P.ECFolder,
			P.EventClass,
			CAST(NULL AS NVARCHAR(255)) AS ParentECFolder
		FROM	@Source P
		WHERE	P.ClassOrder = 1
		UNION ALL
		SELECT	C.ID,
			C.ClassOrder,
			C.ECFolder,
			C.EventClass,
			P.ECFolder
		FROM	Hierarchy P
			INNER JOIN @Source C ON C.ID = P.ID	AND	C.ClassOrder = P.ClassOrder + 1
	)
	SELECT	H.ECFolder,
		H.ParentECFolder,
		H.EventClass
	FROM	Hierarchy H
	ORDER BY H.ParentECFolder,
		H.ECFolder;

Open in new window

0
 
arnoldCommented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
tesupport, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.