locked
To insert multiple records in SQL RRS feed

  • Question

  • I want to insert records in a table in such a way that, i have a string of comma seperated values which is an input parameter to the procedure for this insert. This comma seperated values has to be split into a array of values and each value has to be sent to the select statement selecting one value from a table with where where condition of the one array value each time. a value which is found from this select has to be inserted into another table. For each value of the array a record need to be inserted into this table. Can this be done. Thanks. 
    Sunday, October 14, 2012 5:15 PM

Answers

  • You can also store the data into a table using a function and can use in where condition.

    CREATE  FUNCTION [dbo].[Split_fn]
    (
    	  @ItemList	NVARCHAR(4000)
    	, @delimiter	CHAR(1)
    )
    RETURNS
    	@IDTable	TABLE (	Item	NvarCHAR(100)	COLLATE database_default )
    AS      
    	
    BEGIN    
    	DECLARE @tempItemList VARCHAR(8000)
    	SET @tempItemList = @ItemList
    	DECLARE @i INT    
    	DECLARE @Item VARCHAR(8000)
    	SET @i = CHARINDEX(@delimiter, @tempItemList)
    	WHILE (LEN(@tempItemList) > 0)
    	BEGIN
    		IF @i = 0
    			BEGIN
    			SET @Item = Rtrim(@tempItemList)
    			SET @Item = LTrim(@Item)
    			END
    		ELSE
    			SET @Item = LEFT(@tempItemList, @i - 1)
    		INSERT INTO @IDTable(Item) VALUES(LTrim(@Item))
    		IF @i = 0
    			SET @tempItemList = ''
    		ELSE
    			SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
    		SET @i = CHARINDEX(@delimiter, @tempItemList)
    	END 
    	RETURN
    END
    GO

    You can use the above function in select statement

    AND EmployeeId IN (SELECT Item FROM dbo.Split_fn(@EmployeeId, ','))


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, October 15, 2012 12:50 AM