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).

score 14 · Answer 1 · edited Mar 21 '13 at 23:24

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

score 7 · Accepted Answer · edited May 23 '17 at 12:24

7

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

edited May 23 '17 at 12:24

Community

1
1

answered Mar 19 '12 at 07:02

Mikael Eriksson

136,425
22
210
281

3

+1 For number table approach. But be careful with `master..spt_values`. I wouldn't use it in production code. See http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-me, http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a7cc9252-a1d1-4f51-9c35-7cd9720b507c, http://www.dbforums.com/microsoft-sql-server/1660575-using-master-dbo-spt_values-dangerous.html – Tomek Szpakowicz Mar 19 '12 at 08:20
@TomekSzpakowicz - Good advice. Don't use `master..spt_values` in production code. – Mikael Eriksson Mar 19 '12 at 10:06

score 2 · Answer 3 · answered Mar 19 '12 at 03:44

In my opinion, the best way is to create stored procedure. In stored procedure you should make a loop, which would insert data into table. From your C# application you open a connection to DB, call once a stored procedure and close a connection. On SQL you get best perfomance working with big amount of data.

Here is an example

score 2 · Answer 4 · answered Mar 19 '12 at 07:16

Instead of looping query Create a DataTable and create a stored procedure with User Defined Table Type

CREATE TYPE dtl AS TABLE
( 
          user_master_id INT ,
          role_id INT,
          created_date DATETIME,
          created_by varchar(20)

)

And Stored procedure

CREATE PROCEDURE SPNAME 
@dtl dtl READONLY
AS
INSERT INTO forecast_entry.user_role_xref
        ( user_master_id ,
          role_id ,
          created_date ,
          created_by
        )
SELECT 
          user_master_id ,
          role_id ,
          created_date ,
          created_by
FROM @dtl

Pass DatatTable for @dtl parameter of stored procedure which contains the proper data between 101-255

score 1 · Answer 5 · answered Mar 19 '12 at 03:53

1

if you create a loop in c# it will send same query again and again to database which is not a good idea.you rather create sp and loop there. as suggested by sham

answered Mar 19 '12 at 03:53

Amritpal Singh

1,765
1
13
20

How to insert multiple rows - a loop needed?

5 Answers5

Linked