tsql - How to split string and insert values into table in SQL Server

Question

I have a string like this:

72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX

This is basically 5 values in each of 3 rows (from an ASP.NET grid). I need to split this string apart into 5 columns and 3 rows in a SQL Server table. Individual values are separated by commas and rows by colons.

I found a function to split a string into pieces and I can get the rows out of this string:

declare @testString varchar(100)
set @testString = '72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX'

select *
from dbo.SplitString(@testString, ':')

gives me:

72594206916,2,1/2/08,Tacoma,WA
72594221856,5,5/7/13,San Francisco,CA
72594221871,99,12/30/12,Dallas,TX

This gives me a result set with the three rows (the function outputs a table). Can I call this function again at the same time and insert its output into a table somehow?

Answer 1

assuming your split returns column name item

insert <table> (colname)
select y.item
from dbo.SplitString(@testString, ':') x
cross apply
dbo.SplitString(x.item, ',') y