While Loop in SQL Server for Multiple Entries, Sql Server

While Loop in SQL Server for Multiple Entries

--ENTITY MCDM TABLE CREATION
CREATE TABLE MCDM (Keyid int,  Keyname varchar(25), value1 varchar(25), KeyListId int)

--MCDM VALUES
INSERT INTO MCDM VALUES 
(5000, 'Environment', 'Prototype', 1000), 
(5000, 'Environment', 'Development', 1001),
(5000, 'Environment', 'QA', 1002),
(5000, 'Environment', 'UAT', 1003), 
(5000, 'Environment', 'Production', 1004)

--TABLE
--PROJECTMASTER

CREATE TABLE ProjectMaster 
(ProjectID int identity(1000,1),  ProjectName varchar(25))

--JENKINSMASTER
CREATE TABLE JenkinsMaster 
(JenkinID int IDENTITY(1000, 1), ProjectName varchar(25), EnvironmentID int)

--DECLARATION
DECLARE 
@loopStart int,
@loopEnd int,
@pid int,
@pname varchar(25),
@evname varchar(10),
@evid int
SELECT @loopStart = (SELECT MIN(keylistid) FROM MCDM WHERE keyid = 5000),
@loopEnd = (SELECT MAX(keylistid) FROM MCDM WHERE keyid = 5000),
@pname = 'ELM'

--PROJECTMASTER INSERTION
INSERT INTO ProjectMaster VALUES (@pname)

--JENKINSMASTER LOOP INSERTION
WHILE @loopStart <= @loopEnd
BEGIN
SET @evid = @loopStart;
SET @evname = (Select substring('_'+value1, 1, 5) FROM MCDM WHERE KeyListId = @evid)
INSERT INTO JenkinsMaster VALUES 
(@pname + @evname, @evid)
SELECT 
@loopStart = @loopStart + 1
END
SELECT * FROM MCDM
SELECT * FROM ProjectMaster
SELECT * FROM JenkinsMaster


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development