-- Populate data 3: 2 root, 12 children 4 levels. 3770 rows inserted
USE AdventureWorks
GO
SET NOCOUNT ON
TRUNCATE TABLE dbo.Groups
DECLARE  @TotalLevels int
--SET @TotalLevels=10
SET @TotalLevels=4
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'GroupO_','GroupO'
INSERT @Seed (GroupName,Description) SELECT N'GroupP_','GroupP'
INSERT @Seed (GroupName,Description) SELECT N'GroupQ_','GroupQ'
INSERT @Seed (GroupName,Description) SELECT N'GroupR_','GroupR'
INSERT @Seed (GroupName,Description) SELECT N'GroupS_','GroupS'
INSERT @Seed (GroupName,Description) SELECT N'GroupT_','GroupT'
INSERT @Seed (GroupName,Description) SELECT N'GroupU_','GroupU'
INSERT @Seed (GroupName,Description) SELECT N'GroupV_','GroupV'
INSERT @Seed (GroupName,Description) SELECT N'GroupW_','GroupW'
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