Insert SQL Server 2008 with WHILE LOOP

Question

I have existing entries, for example

ID Hospital ID Email Description 1 15 [e-mail] Sample Description 2 15 [e-mail] Random Text 

I need to use the WHILE loop to insert rows with the hospital ID changed to a specific value or 32 in this case, while the rest (and not the identifiers that are automatically generated) remain constant.

Then it should look like

 ID Hospital ID Email Description 1 15 [e-mail] Sample Description 2 15 [e-mail] Random Text 3 32 [e-mail] Sample Description 4 32 [e-mail] Random Text 

Note that in the example above there are now two new lines with the identifier and the hospital identifier. ID is automatically generated.

I have several tables where I need to make the same updates. I don't want to use a cursor if I can do this with a while loop.

EDIT An abandoned while loop as an easier solution was provided in the accepted answer.

Answer 1

Assuming the 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 thinking in terms of sets instead.

Answer 2

First of all, I would like to say that I am 100% in agreement with John Saunders that you should avoid loops in SQL in most cases, especially in production.

But sometimes, as one time, to fill out a table with hundreds of records for testing purposes, IMHO it's just OK to allow yourself to use a loop.

For example, in your case, to fill out a table using records with hospital identifiers from 16 to 100 and make individual letters and descriptions different, which you could use

 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 the result will be

 ID Hospital ID Email Description ---- ----------- ---------------- --------------------- 1 16 [e-mail] Sample Description16 2 17 [e-mail] Sample Description17 ... 84 99 [e-mail] Sample Description99