I have existing records like
ID Hospital ID Email Description
1 15 [email protected] Sample Description
2 15 [email protected] Random Text
I need to use a WHILE loop to insert rows with Hospital ID changing to a specific value or 32 in this case, while the others(not ID as it is auto generated) remaining constant.
It should then look like
ID Hospital ID Email Description
1 15 [email protected] Sample Description
2 15 [email protected] Random Text
3 32 [email protected] Sample Description
4 32 [email protected] Random Text
Notice the above now has two new rows with ID and Hospital ID different. ID is auto generated.
I have several tables where I need to make the same updates. I don't want to use cursor if I can do this with a while loop.
EDIT Abandoned while loop as a simpler solution was provided in the accepted answer.
This question is related to
sql
sql-server
while-loop
First of all I'd like to say that I 100% agree with John Saunders that you must avoid loops in SQL in most cases especially in production.
But occasionally as a one time thing to populate a table with a hundred records for testing purposes IMHO it's just OK to indulge yourself to use a loop.
For example in your case to populate your table with records with hospital ids between 16 and 100 and make emails and descriptions distinct you could've used
CREATE PROCEDURE populateHospitals
AS
DECLARE @hid INT;
SET @hid=16;
WHILE @hid < 100
BEGIN
INSERT hospitals ([Hospital ID], Email, Description)
VALUES(@hid, 'user' + LTRIM(STR(@hid)) + '@mail.com', 'Sample Description' + LTRIM(STR(@hid)));
SET @hid = @hid + 1;
END
And result would be
ID Hospital ID Email Description
---- ----------- ---------------- ---------------------
1 16 [email protected] Sample Description16
2 17 [email protected] Sample Description17
...
84 99 [email protected] Sample Description99
Source: Stackoverflow.com