SQL Server 2008 Insert with WHILE LOOP

Question

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.

Answer 1

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 

Answer 2

Assuming that ID is an identity column:

INSERT INTO TheTable(HospitalID, Email, Description)
SELECT 32, Email, Description FROM TheTable
WHERE HospitalID <> 32

Try to avoid loops with SQL. Try to think in terms of sets instead.