Comma separted string into columns using SQL Server 2008 R2

Question

I want to insert the bulk values into the table by using stored procedure.

Here is the following table called m1 with two fields:

create table m1
(
    cola varchar(50),
    colb varchar(50)
);

Procedure m1_test to insert the values into the table:

create procedure m1_test
    @stringCola varchar(max),
    @stringColb varchar(max)

AS

    INSERT INTO m1 values(@stringCola,@stringColb);

GO

The above procedure is fine for inserting single values as shown below:

---EXECUTE SP
EXECUTE m1_test @stringCola = 'A1',@stringColb = 'Z1';

But If I want to insert the more values at a time in single string like shown below:

---EXECUTE SP
EXECUTE m1_test @stringCola = 'A1,A2,A3',@stringColb = 'Z1,Z2,Z3';

The output should be:

 cola   colb
 ------------
 A1     Z1
 A2     Z2
 A3     Z3

This is very bad design. Better add table valued parameter. – Giorgi Nakeuri May 25 '15 at 13:16 — Giorgi Nakeuri, May 25 '15 at 13:16

score 1 · Accepted Answer · edited May 23 '17 at 11:51

First you need to create a table valued function which will return the comma separated values in the form of a table.

Implement the below code for the function, which I found here, with a slight modification to return an identity column based on which you will later do a JOIN to get a tuple of the 1st value for column A and the first value for column B (A1, Z1) and so on the 2nd, 3rd etc. :

CREATE FUNCTION Split (@InputString VARCHAR(8000), @Delimiter VARCHAR(50))

RETURNS @Items TABLE (ID INTEGER IDENTITY(1,1), Item VARCHAR(8000))

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item VARCHAR(8000)
      DECLARE @ItemList VARCHAR(8000)
      DECLARE @DelimIndex INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

---- Set Permissions
--GRANT SELECT ON Split TO UserRole1
--GRANT SELECT ON Split TO UserRole2
--GO

Now, after creating this function, modify your stored procedure to:

CREATE PROCEDURE m1_test @stringCola VARCHAR(max), @stringColb VARCHAR(max)
AS
INSERT INTO m1
SELECT A.Item, B.Item
FROM Split(@stringColA, ',') A
    INNER JOIN Split(@stringColB, ',') B ON A.ID = B.ID
GO

What if I have more values to insert into multiple columns ? Like `5 strings into 5 columns` — MAK, May 25 '15 at 13:21
@MAK Tough luck, change the code of your stored procedure to accomodate multiple values and use multiple `JOIN`s to generate the result set which is later to be inserted into `m1`. Just like I did in my example above.. expand that to accomodate as many values as you want. — Radu Gheorghiu, May 25 '15 at 13:24
But as @GiorgiNakeuri said, this is a bad design and does not scale. — Radu Gheorghiu, May 25 '15 at 13:24

score 1 · Answer 2 · answered May 25 '15 at 13:41

As I said in comments this is bad design, but you can do it this way:

INSERT INTO m1 values
SELECT  a.d ,
        b.d
FROM (SELECT Split.a.value('.', 'VARCHAR(100)') d ,
             ROW_NUMBER() OVER ( ORDER BY ( SELECT   NULL ) ) rn
      FROM (SELECT CAST('<M>' + REPLACE(@stringCola, ',', '</M><M>') + '</M>' AS XML) d) A
      CROSS APPLY d.nodes('/M') AS Split ( a )
     ) a
JOIN 
     (SELECT Split.a.value('.', 'VARCHAR(100)') d ,
             ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) rn
      FROM (SELECT CAST('<M>' + REPLACE(@stringColb, ',', '</M><M>') + '</M>' AS XML) d ) A
      CROSS APPLY d.nodes('/M') AS Split ( a )
     ) b ON a.rn = b.rn

Okay! I accept that is a bad design. Can it is possible in you script if I give two strings like: `@stringCola = 'A1,A2'` and `@StringColb = 'B1'` and it has to store like: `Cola = A1,A2` and `Colb = B1,Null` — MAK, May 25 '15 at 13:48

Comma separted string into columns using SQL Server 2008 R2

2 Answers2