Arham Ranjha Arham Ranjha - 6 months ago 17
SQL Question

How to add values from a string in SQL Server 2008 using a stored procedure?

How can I insert values from a string like this

1,2,3,4;7,3,8,4;3,9,0,4;
into SQL Server 2008 using a stored procedure, where
;
represent a start of new row and
,
represent a new column? e.g the values 1 2 3 4 will be inserted to there respective columns and after ';' the next values i:e 7 3 8 4 will be insert to same columns "7" into first columns and so on

I've found some answers but they've confused me more than ever.

Edit:

Most of the answer i found were inserting values in just two columns by using LTRIM and RTRIM, but i have a varring number of columns on different tables.
I hope this explains my problem

Answer

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
Comments