SQL Server 2008 Insert with WHILE LOOP

Question

I have existing records like

ID    Hospital ID     Email                Description 
1       15         abc@e.com           Sample Description
2       15         def@dd.com          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         abc@e.com           Sample Description
2       15         def@dd.com          Random Text
3       32         abc@e.com           Sample Description
4       32         def@dd.com          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.

Of course. I don't know how to write the insert query. I tried declaring @hospitalid int and the looping with while loop with @@RowCount. — Mukus, Mar 03 '13 at 04:38
@navi - If I knew how to write I wouldn't ask the question. Try to be constructive. — Mukus, Mar 03 '13 at 04:45

score 21 · Answer 1 · answered Mar 03 '13 at 05:27

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          user16@mail.com  Sample Description16 
2    17          user17@mail.com  Sample Description17 
...                                                    
84   99          user99@mail.com  Sample Description99

The accepted answer slightly modified does what I am looking for. I was not trying to increase the HospitalID. My question shows the example of what I was looking to get. — Mukus, Mar 03 '13 at 08:21
@TejaswiRana The question shows example but not intent behind it. You can use const value for HospitalID `32` in my example if you want to. But if you want to just duplicate existing set of records exactly as they are while changing value of one column then @JohnSaunders' solution is the best way to go. — peterm, Mar 03 '13 at 08:48

score 12 · Accepted Answer · answered Mar 03 '13 at 04:58

12

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.

answered Mar 03 '13 at 04:58

John Saunders

160,644
26
247
397

That just works. Thank you. Just curious to learn though, can you think of a way to do with WHILE LOOP? would that mean creating a temp table to store temp values? – Mukus Mar 03 '13 at 05:06
3

I would never use a `WHILE` loop. Whenever I find my self using code like that, I stop what I'm doing and start over. – John Saunders Mar 03 '13 at 05:08

SQL Server 2008 Insert with WHILE LOOP

2 Answers2

Linked