none
Inserting multiple rows from Comma separated list in a variable

    Question

  • I have the following T-SQL statement that is used to add row into my table:

     

    CREATE PROCEDURE [dbo].[AddEPNItem] 
    	-- Add the parameters for the stored procedure here
    	@Item varchar(50) , 
    	@EPNID int 
    AS
    BEGIN
    	DECLARE @itemCount int
    	--check for a duplicate part, if one is already there, then just return
    	SET @itemCount = (SELECT COUNT(EPNITEMID) FROM EPNItem WHERE EPNID = @EPNID AND Item = @Item)
    	IF @itemCount > 0 
    		BEGIN
    			RETURN
    		END
    	ELSE
    		BEGIN
    			INSERT INTO
    					EPNITEM (Item,EPNID)
    			VALUES
    					(@Item,@EPNID)
    	END
    END
    

    I need to modify this so the @Item can be passed in as a comma separated string of Pieces to add (all of them will have the same EPNID) and insert one row for each item in the list.  I am running on SQL Server 2005 for this operation so the table object isn't available.  Any ideas on how to do this?  There will be anywhere from 2 to N items in the list.  If there is a single item in the list this proc is called in its place.  

     

    Thanks.

     

    Katghoti

    Monday, November 15, 2010 4:55 PM

Answers

  • Take a look at this article Arrays and Lists in SQL Server 

    Having a splitting function in your possession, the insert will become

     

    ;with Items as (select Value as Item from dbo.fnSplit(@Item,','))
    
    insert into EpnItem (Item, EpnID)
    
    select Item, @EpnID
    
    from Items where not exists (select 1 from EPNItem E where E.EpnID = @EpnID  and E.Item = Items.Item)
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Willy Taveras Monday, November 15, 2010 5:12 PM
    • Marked as answer by KJian_ Friday, December 03, 2010 8:28 AM
    Monday, November 15, 2010 5:12 PM
    Moderator

All replies

  • This code was in MySQL. You've to play around to get it work in SQL Server. but it works fine with MySQL.

     

    SET @id = cart_id;

       SET @sql1 = CONCAT(
                'INSERT INTO cartCouponAdd (cartID, couponID) VALUES (@id,',
                REPLACE (coupon_id, ',', '),(@id,'),
                ')'
                );
       
       PREPARE myStmt FROM @sql1;
                
       EXECUTE myStmt;

    Monday, November 15, 2010 5:07 PM
  • Take a look at this article Arrays and Lists in SQL Server 

    Having a splitting function in your possession, the insert will become

     

    ;with Items as (select Value as Item from dbo.fnSplit(@Item,','))
    
    insert into EpnItem (Item, EpnID)
    
    select Item, @EpnID
    
    from Items where not exists (select 1 from EPNItem E where E.EpnID = @EpnID  and E.Item = Items.Item)
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Willy Taveras Monday, November 15, 2010 5:12 PM
    • Marked as answer by KJian_ Friday, December 03, 2010 8:28 AM
    Monday, November 15, 2010 5:12 PM
    Moderator
  • Take a look at this article (splitting function) :

    http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx


    Best regards
    Monday, November 15, 2010 5:24 PM