Insert multiple sql rows via stored proc

Question

I have looked a some related topics but my question isn't quite answered:

I have the following kind of setup when running my stored procedure in the code behind for my web application. The thing is I am now faced with the possibility of inserting multiple products and I would like to do it all in one ExecuteNonQuery rather than do a foreach loop and run it n number of times.

I am not sure how to do this, or if it can be, with my current setup.

The code should be somewhat self explanatory but if clarification is needed let me know. Thanks.

SqlDatabase database = new SqlDatabase(transMangr.ConnectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "proc_name", useStoredProc);

database.AddInParameter(commandWrapper, "@ProductID", DbType.Int32, entity._productID);
database.AddInParameter(commandWrapper, "@ProductDesc", DbType.String, entity._desc);
...more parameters...


Utility.ExecuteNonQuery(transMangr, commandWrapper);

Proc

ALTER PROCEDURE [dbo].[Products_Insert]
-- Add the parameters for the stored procedure here
         @ProductID int,
         @Link      varchar(max)
         @ProductDesc          varchar(max)
         @Date          DateTime
    AS BEGIN

SET NOCOUNT ON;


INSERT INTO [dbo].[Prodcuts]
    ( 
        [CategoryID],
        [Link],
        [Desc],
        [Date]
    )
VALUES
    (
        @ProductID,
        @Link,
        @ProductDesc,
        @Date
    )

END

So have multiple `INSERT` statements inside a single `ExecuteNonQuery()` ?? — Brad M, Jun 17 '13 at 18:58
The stored procedure has the insert statements and takes in the added parameters to insert like that. I don't think I can do multiple inserts that way? — B-M, Jun 17 '13 at 19:00
Might be helpful to see the stored proc... Or you could create the insert commands in a `foreach` loop, then use a `TransactionScope` to execute them all at once... though with stored procedure i'm not really sure that will work. — Evan L, Jun 17 '13 at 19:06

score 1 · Accepted Answer · answered Jun 17 '13 at 19:07

You should be fine running your stored procedure in a loop. Just make sure that you commit rarely, not after every insert.

For alternatives, you have already found the discussion about loading data.

Personally, I like SQL bulk insert of the form insert into myTable (select *, literalValue from someOtherTable); But that will probably not do in your case.

score 0 · Answer 2 · answered Jun 17 '13 at 19:22

You could pass all your data as a table value parameter - MSDN has a pretty good write up about it here

Something along the lines of the following should work

CREATE TABLE dbo.tSegments (

SegmentID BIGINT NOT NULL CONSTRAINT pkSegment PRIMARY KEY CLUSTERED,
SegCount BIGINT NOT NULL

);

CREATE TYPE dbo.SegmentTableType AS TABLE ( SegmentID BIGINT NOT NULL );

CREATE PROCEDURE dbo.sp_addSegments

  @Segments dbo.SegmentTableType READONLY

AS

BEGIN MERGE INTO dbo.tSegments AS tSeg

USING @Segments AS S
ON tSeg.SegmentID = S.SegmentID
WHEN MATCHED THEN UPDATE SET T.SegCount = T.SegCount + 1
WHEN NOT MATCHED THEN INSERT VALUES(tSeg.SegmentID, 1);

END

score 0 · Answer 3 · answered Jun 17 '13 at 20:55

Define the commandWrapper and parameters for the command outside of the loop and then with in the loop you just assign parameter values and execute the proc.

SqlDatabase database = new SqlDatabase(transMangr.ConnectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "proc_name", useStoredProc);

database.AddInParameter(commandWrapper, "@ProductID", DbType.Int32 );
database.AddInParameter(commandWrapper, "@ProductDesc", DbType.String);
...more parameters...

foreach (var entity in entitties)
{
    database.SetParameterValue(commandWrapper, "@ProductID",entity._productID);
    database.SetParameterValue(commandWrapper, "@ProductDesc",entity._desc);
    //..more parameters...
    Utility.ExecuteNonQuery(transMangr, commandWrapper);
}

score 0 · Answer 4 · answered Apr 09 '19 at 11:52

Not ideal from a purist way of doing things, but sometimes one is limited by frameworks and libraries, and that you are forced to call stored procedures in a certain way, bind parameters in a certain way, and that connections are managed by pools as part of your framework.

In such circumstances, a method we have found to work is to simply write your stored procedure with a lot of parameters, usually a name followed by a number, e.g. @ProductId1, @ProductDesc1, @ProductId2, @ProductDesc2 up to a number you decide, possibly say 32.

You can use some form of scripting language to produce the lines for this.

You can get the stored procedure to insert all the values first into a table parameter that allows nulls, then do bulk inserts / merges on this data in a way similar to Johnv2020's answer. You might remove the null rows first.

It will usually be more efficient than doing it one at a time (partly because of the database operations itself, and partly because of your framework's overheads in getting the connection to call the procedure etc.)

Insert multiple sql rows via stored proc

4 Answers4