none
Insert multiple records in a DB

    Question

  • I want to insert multiple records in a database and the stored proc below is inserting only one? How should I modify it?

    create PROCEDURE [dbo].sp_MethodInsertUpdate
    
    (
    	
    	@Use_RID int,
    	@Method_RID int,
    	@Group_RID int,
    	@Use_Name varchar(50),
    	@Use_Description varchar(500),
    	@Use_Help varchar(500),
    	@Use_IsRequired bit,
    	@Use_CanDelete bit,
    	@DateModified datetime,
    	@Entity_Type varchar(10) = 'M',
        @Use_IsVisible bit = 0,
        @Use_IsIMSVisible bit = 0,
        @Use_IsParticipantRequired bit = 0,
        @Use_IsParticipantVisible bit = 0,
        @Use_IsIMSParticipantVisible bit = 0,
        @Use_IsFax bit = 0
    )
    AS
    SET NOCOUNT ON
    declare @groupRID int
    set @groupRID = @Group_RID
    
    If(@Group_RID=0)
    BEGIN
       SELECT @groupRID = ISNULL(MAX([Group_RID]),0)+1 FROM Contact_Default_Grouping
       SET IDENTITY_INSERT Contact_Default_Grouping ON
       Insert into Contact_Default_Grouping (Group_RID) values (@groupRID)
       SET IDENTITY_INSERT Contact_Default_Grouping OFF
    END
    
    IF @Use_RID=0
    BEGIN
       INSERT INTO Contact_Use
       (	
    	   Method_RID,
    	   Group_RID,
    	   Use_Name,
    	   Use_Description,
    	   Use_Help,
    	   Use_IsRequired,
    	   Use_CanDelete,
    	   DateModified,
    	   Entity_Type,
           Use_IsIMSVisible,
           Use_IsVisible,
           Use_IsParticipantRequired,
           Use_IsParticipantVisible,
           Use_IsIMSParticipantVisible,
    	   Use_IsFax
       )
       VALUES
       (
    	   @Method_RID,
    	   @groupRID,
    	   @Use_Name,
    	   @Use_Description,
    	   @Use_Help,
    	   @Use_IsRequired,
    	   @Use_CanDelete,
    	   @DateModified,
    	   @Entity_Type,
           @Use_IsIMSVisible,
           @Use_IsVisible,
           @Use_IsParticipantRequired,
           @Use_IsParticipantVisible,
           @Use_IsIMSParticipantVisible,
    	   @Use_IsFax
       )
    END
    ELSE
    BEGIN
       UPDATE Contact_Use SET
    	   Group_RID = @groupRID,
    	   Use_Name = @Use_Name,
    	   Use_Description = @Use_Description,
    	   Use_Help = @Use_Help,
    	   Use_IsRequired = @Use_IsRequired,
    	   Use_CanDelete = @Use_CanDelete,
    	   DateModified = @DateModified,
    	   Entity_Type = @Entity_Type,
           Use_IsIMSVisible = @Use_IsIMSVisible,
           Use_IsVisible =@Use_IsVisible,
           Use_IsParticipantRequired = @Use_IsParticipantRequired,
           Use_IsParticipantVisible = @Use_IsParticipantVisible,
           Use_IsIMSParticipantVisible = @Use_IsIMSParticipantVisible,
    	   Use_IsFax = @Use_IsFax
       WHERE Use_RID = @Use_RID
    END
    SET NOCOUNT OFF      
    
    

    Wednesday, February 27, 2013 6:48 PM

Answers

  • Using IDENTITY_INSERT here really is not good.  The whole point of IDENTITY is that you don't have to do MAX(ID)+1 to generate a surrogate key value.  Just insert the row and retrieve the generated IDENTITY value with SCOPE_IDENTITY().

    To answer your question, change the stored procedure to take a Table Valued Parameter instead of a single row of scalar values.

    See Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 28, 2013 3:08 AM

All replies

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Please do not use the 1970's Sybase “sp_” prefix unless this is a system procedure. The correct convention for a procedure name is “<verb>_<object>”, but this minor compared the other fundamental errors you have in this mess. BIT flags in SQL?  NO! That was assembly language. 

    >> I want to insert multiple records [sic: rows are not records!] in a database and the stored procedure below is inserting only one? How should I modify it? <<

    You put the parameter list into a row constructor, so of course there is only one row. 

    Look at my credentials. Consider how bad this code has to be for me to tell you to throw out your entire schema. Your schema, coding and mindset are fundamentally wrong. Bit flags, IDENTITY, procedrural IF-THEN flow logic, etc. You have no idea how to write SQL or how RDBMS works. Please stop trying to program SQL until you learn how.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, February 28, 2013 2:26 AM
  • Using IDENTITY_INSERT here really is not good.  The whole point of IDENTITY is that you don't have to do MAX(ID)+1 to generate a surrogate key value.  Just insert the row and retrieve the generated IDENTITY value with SCOPE_IDENTITY().

    To answer your question, change the stored procedure to take a Table Valued Parameter instead of a single row of scalar values.

    See Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 28, 2013 3:08 AM