﻿--random sample from msdn library: http://msdn.microsoft.com/en-us/library/bb630263.aspx
with paths (
	path
	,EmployeeID
	)
as (
	-- This section provides the value for the root of the hierarchy
	select hierarchyid::GetRoot() as OrgNode
		,EmployeeID
	from #Children as C
	where ManagerID is null
	
	union all
	
	-- This section provides values for all nodes except the root
	select CAST(p.path.ToString() + CAST(C.Num as varchar(30)) + '/' as hierarchyid)
		,C.EmployeeID
	from #Children as C
	join paths as p on C.ManagerID = P.EmployeeID
	)
insert NewOrg (
	OrgNode
	,O.EmployeeID
	,O.LoginID
	,O.ManagerID
	)
select P.path
	,O.EmployeeID
	,O.LoginID
	,O.ManagerID
from EmployeeDemo as O
join Paths as P on O.EmployeeID = P.EmployeeID
go

--similar sample, with 2 CTEs in the same query
begin
	with FirstCTE
	as (
		select 1 as FirstColumn
		)
		,SecondCTE (AnotherColumn)
	as (
		select 2
		)
	select *
	from FirstCTE
	
	union
	
	select *
	from SecondCTE
end
go


