SQL insert data to other table after split string

Question

I have a table WCA:

ID TYPE    ..
1  *1*3*5*
2  *1*5*
..

Now i want move data to new table WCA_TYPE:

ID WCA_ID TYPE
1    1     1
2    1     3
3    1     5
4    2     1
5    2     5
..

ID here is auto increase.

How to write sql in MS SQL server to split type from old table to multi type & insert it into new table.

Answer 1

DECLARE @CurID INT, @MaxID INT, @t VARCHAR(200)

SELECT @CurID = 1, @MaxID = MAX(ID) FROM WCA

WHILE @CurID <= @MaxID
BEGIN
  SELECT @t = TYPE
  FROM WCA
  WHERE ID = @CurID

  ;WITH Pieces([Pos], [start], [stop]) AS (
      SELECT 1, 1, CHARINDEX('*', @t)
      UNION ALL
      SELECT [Pos] + 1, [stop] + 1, CHARINDEX('*', @t, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
    INSERT INTO WCA_TYPE(WCA_ID, TYPE)
    SELECT @CurID, T.Value
    FROM
    ( SELECT [Pos], SUBSTRING(@t, start, CASE WHEN [stop] > 0 THEN [stop]-[start] ELSE 4000 END) AS [Value]
      FROM Pieces
    ) T 
    WHERE T.Value <> '' 


  SET @CurID = @CurID + 1
END

SELECT *
FROM WCA_TYPE

look this fiddle