[Solved] How to add values from a string in SQL Server 2008 using a stored procedure? [closed]

Question

I have prepared a SQL script for you which splits the input string twice then converts is into row data, and finally inserts into a database table

To split string data, I used this SQL split string function
First of all, you need to create this function on your database.

Unfortunately, if you do not use SQL Server 2016 we have to create our split function

I have used SQL Server CTE query structure to manage all these steps in a single statement instead of using SQL sub-select statements or temp tables.

To understand multiple-CTE structures please refer to above tutorial

I hope it helps
Please check below script on your test database first

declare @str varchar(max) = '1,2,3,4;7,3,8,4;3,9,0,4;'

create table stringtorows (id int, col1 int, col2 int, col3 int, col4 int)

;with cte as (
select
    id rowid, val rowdata
from dbo.split(@str,';')
where val <> ''
), cte2 as (
select
    rowid, id colid, val coldata
from cte
cross apply dbo.Split(rowdata,',')
), cte3 as (
select
    rowid,
    case when colid = 1 then coldata end col1,
    case when colid = 2 then coldata end col2,
    case when colid = 3 then coldata end col3,
    case when colid = 4 then coldata end col4
from cte2
)
insert into stringtorows
select
    rowid, max(col1) col1, max(col2) col2, max(col3) col3, max(col4) col4
from cte3
group by rowid

select * from stringtorows

Accepted Answer