How to insert multiple rows - a loop needed?

Question

I have the following statement:

insert into forecast_entry.user_role_xref
        ( user_master_id ,
          role_id ,
          created_date ,
          created_by
        )
values
        ( 276 , -- user_master_id - int
          101 , -- role_id - int
          getdate() , -- created_date - datetime
          'MICHAELSK'  -- created_by - varchar(20)
        )

I need to generate a row for role_id 101-355 (so the same statement above, except repeated with the role_id incrementing). What would be the best way to do this? To get the job done I'm intending on writing a quick C# application that will have a loop but I'm sure this isn't the best way and hope to learn something here to avoid having to do that in future (as I'm sure this kind of scenario is common).

Answer 1

Here is what I use, just modify as needed. Here, I add a bunch of sequence numbers to a table using a loop variable:

USE MyDB

GO

DECLARE @MyCounter as INT

SET @MyCounter = 1  -- to use this multiple times you can just

                    -- change the starting number and run again
                    -- if you do not want duplicate numbers

WHILE @MyCounter < 1000  -- any value you want

 BEGIN

    INSERT INTO [MyDB].[dbo].[MyTable]
        ([NumberField])
    VALUES
        (@MyCounter)  -- insert counter value into table

        set @MyCounter = @MyCounter + 1;  -- increment counter

END

Answer 2

You should make use of numbers table and if you don't have one you can use master..spt_values like this:

insert into forecast_entry.user_role_xref
        ( user_master_id ,
          role_id ,
          created_date ,
          created_by
        )
select 276, -- user_master_id - int
       number, -- role_id - int
       getdate() , -- created_date - datetime
       'MICHAELSK'  -- created_by - varchar(20)
from master..spt_values
where type = 'P' and
      number between 101 and 355