Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Insert multiple records in a DB

Traitée Insert multiple records in a DB

  • Wednesday, February 27, 2013 6:48 PM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Thursday, February 28, 2013 2:26 AM
     
     
    Sign In to Vote
    0

    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 3:08 AM
     
     Answered
    Sign In to Vote
    0

    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/