Adding Items in Comma-separated String into Separate Rows in SQL Server

In this post, I’ll show how to insert 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:

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. Here, I'm assuming that the identity property of the table is set to true and increments by '1' for every insert action performed on the table.

The Stored Procedure is as follows:

CREATE PROCEDURE AddCommaSeparatedUsersToTable
(
      @UserNames NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @DELIMITER NCHAR(1)   --delimiter used to separate the usernames
DECLARE @tmpUserNames NVARCHAR(MAX)
SET @tmpUserNames = @UserNames
SET @DELIMITER = ‘,’       --Delimiter is a comma
DECLARE @commaIndex INT    
DECLARE @singleUserName NVARCHAR(MAX)
--singleUserName is the variable which holds each item in the comma-separated string

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.

Hope this helps!!