Clever Geek Handbook

How to insert multiple lines - need a loop?

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 string for role_id 101-355 (which is why the same operator above, except for incrementing role_id iterations). What would be the best way to do this? To make this work, I intend to write a quick C # application that will have a loop, but I am pretty sure this is not the best way and hope to learn something here to avoid having to do it in the future (that this scenario is common).

+3


source to share


5 answers


You should be using the numbers table , and if you don't have one, you can use it 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
+6


source


This is what I use, just change it as needed. Here I am adding a bunch of ordinal numbers to the 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
+8


source


In my opinion, the best way is to create a stored procedure. In the stored procedure, you have to make a loop that will insert data into the table. From your C # app, you open a DB connection, call the stored procedure once, and close the connection. With SQL, you get the best experience for working with large amounts of data.

Here's an example

+2


source


if you create a loop in C # it will send the same request over and over to the database, which is not very good. You'd rather create sp and loop there. as suggested by trick

+1


source


Instead of requesting a loop Build DataTable

and Build stored procedure

withUser Defined Table Type 

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

)

And the 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 the @dtl parameter of the stored procedure that contains the correct data between 101-255

+1


source






More articles:


All Articles