Loop through a split string variable to insert rows in a stored procedure in SQL Server 2008

Question

I am working on SQL Server 2008 to create a stored procedure that:

1. takes a string variable like this: '1,2,3'
2. splits the string using a table-valued function to get each value separately
3. and then inserts each value into a new row in a table

What I am trying to do is something like this:

WHILE (select vlaue FROM dbo.SplitString('1,2,3',',')) has rows
insert into TableName (col1,col2) values (col1Data, value)

I am having a hard time trying to find the right syntax for this.

Answer 1

I use this Table-valued function:

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
 RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
  SELECT 1, 1, CHARINDEX(@sep, @s)
  UNION ALL
  SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
  FROM Pieces
  WHERE stop > 0
)
SELECT pn,
  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO

Which takes a string with a separator and returns a table with two columns the first returns a 1-based position and the second the element at that position in the string:

Usage:

SELECT * FROM dbo.Split(',', '1,2,3')

Returns:

pn  s
1   1
2   2
3   3

To Insert results into a table:

INSERT INTO TableName (Col1)
SELECT S FROM dbo.Split(',', '1,2,3)

For your specific example change your syntax to be:

   insert into TableName (col1,col2) 
   select col1Data, value FROM dbo.SplitString('1,2,3',',')

Answer 2

The typical INSERT INTO ... SELECT ... should do:

INSERT INTO TableName (col1,col2)
SELECT @col1Data,value FROM dbo.SplitString('1,2,3',','))