Insert Comma Seperated String Data into Rows in SQLServer AspDotnet

Hi,
In this post, I’ll show how to insert a items in a comma-separated string into separate rows in a table. Consider for example we have a comma-separated string such as “amogh, anish, anvesh, uday”. after inserting into the table, the output should be like:

Expected Result

I have written a stored procedure which will take the comma-separated string as input and insert  a new row into the table for each item in the string.

The Stored procedure is as follows:

CREATE PROCEDURE AddCommaSeparatedUsersToTable
(
      @UserNames NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @DELIMITER NCHAR(1)  
DECLARE @tmpUserNames NVARCHAR(MAX)
SET @tmpUserNames = @UserNames
SET @DELIMITER = ','                
DECLARE @commaIndex INT    
DECLARE @singleUserName NVARCHAR(MAX)             
SELECT @commaIndex = 1    
      IF LEN(@tmpUserNames)<1 OR @tmpUserNames IS NULL  RETURN    
WHILE @commaIndex!= 0    
BEGIN    
      SET @commaIndex= CHARINDEX(@DELIMITER,@tmpUserNames)    
      IF @commaIndex!=0    
            SET @singleUserName= LEFT(@tmpUserNames,@commaIndex- 1)    
      ELSE    
            SET @singleUserName = @tmpUserNames    
      IF(LEN(@singleUserName)>0)
      BEGIN                        
            INSERT INTO SampleUserTable
            (
                  UserName
            )
            VALUES
            (
                  @singleUserName
            )
      END
      SET @tmpUserNames = RIGHT(@tmpUserNames,LEN(@tmpUserNames) - @commaIndex)    
      IF LEN(@tmpUserNames) = 0 BREAK    
END
END

This procedure will insert each item in the comma-separated string (UserNames, given as input parameter to the procedure) into the table “SampleUserTable” in separate rows.