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

User · Accepted Answer

Assuming that ID is an identity column   INSERT INTO TheTable HospitalID  Email  Description  SELECT 32  Email  Description FROM TheTable WHERE HospitalID  lt  gt  32   Try to avoid loops with SQL  Try to think in terms of sets instead

User · Answer

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  lt  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

[sql] SQL Server 2008 Insert with WHILE LOOP

Examples related to sql

Examples related to sql-server

Examples related to while-loop