-- Populate data 2: 2 root, 3 children 10 levels. 59048 rows inserted
USE AdventureWorks
GO
SET NOCOUNT ON
TRUNCATE TABLE dbo.Groups
DECLARE  @TotalLevels int
SET @TotalLevels=10
DECLARE @Seed TABLE (GroupName nvarchar(100) NOT NULL, Description nvarchar(150) NOT NULL)
DECLARE @Parents1 TABLE (GroupID int NOT NULL,GroupName nvarchar(100) NOT NULL)
DECLARE @Parents2 TABLE (GroupID int NOT NULL,GroupName nvarchar(100) NOT NULL)
DECLARE @Level int
INSERT @Seed (GroupName,Description) SELECT N'GroupX_','GroupX'
INSERT @Seed (GroupName,Description) SELECT N'GroupY_','GroupY'
INSERT @Seed (GroupName,Description) SELECT N'GroupZ_','GroupZ'
INSERT dbo.Groups (ParentGroupID,GroupName,Description)
OUTPUT INSERTED.GroupID,INSERTED.GroupName INTO @Parents1
SELECT NULL,'GroupA_L1','Root 0'
INSERT dbo.Groups (ParentGroupID,GroupName,Description)
OUTPUT INSERTED.GroupID,INSERTED.GroupName INTO @Parents1
SELECT NULL,'GroupB_L1','Root 1'
SET @Level=2
WHILE @Level<=@TotalLevels
BEGIN
	IF @Level%2=0
	BEGIN
		INSERT dbo.Groups (ParentGroupID,GroupName,Description)
		OUTPUT INSERTED.GroupID,INSERTED.GroupName INTO @Parents2
		SELECT P.GroupID,P.GroupName+N'.'+S.GroupName+CAST(@Level as nvarchar(20)),S.Description+' in Level '+CAST(@Level as nvarchar(20))
		FROM @Parents1 P CROSS JOIN @Seed S
		DELETE @Parents1
	END
	ELSE
	BEGIN
		INSERT dbo.Groups (ParentGroupID,GroupName,Description)
		OUTPUT INSERTED.GroupID,INSERTED.GroupName INTO @Parents1
		SELECT P.GroupID,P.GroupName+N'.'+S.GroupName+CAST(@Level as nvarchar(20)),S.Description+' in Level '+CAST(@Level as nvarchar(20))
		FROM @Parents2 P CROSS JOIN @Seed S
		DELETE @Parents2
	END
	SET @Level=@Level+1
END
-- SELECT * FROM dbo.Groups