[sql] SQL Server 2008 Insert with WHILE LOOP

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

The answer is


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 

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to while-loop

While, Do While, For loops in Assembly Language (emu8086) MySQL Insert with While Loop Python loop to run for certain amount of seconds How to break a while loop from an if condition inside the while loop? How to find sum of several integers input by user using do/while, While statement or For statement Python: How to keep repeating a program until a specific input is obtained? Creating multiple objects with different names in a loop to store in an array list ORA-06502: PL/SQL: numeric or value error: character string buffer too small How to break out of a loop in Bash? for or while loop to do something n times