separate comma separated values and store in table in sql server -

separate comma separated values and store in table in sql server -

i having stored procedure gets comma separated value input. need separate , needs store in table individual rows.

let input sp :

rule_id listtype_id values 1 2 319,400,521,8465,2013

i need store in table called distributionrule_x_listtype in below format:

rule_id listtype_id value 1 2 319 1 2 400 1 2 521 1 2 8465 1 2 2013

my sp looks below:

alter procedure [dbo].[spinsertdistributionrulelisttype] (@rule_id int, @listtype_id int, @values varchar(max)=null ) begin insert distributionrule_x_listtype (rule_id,listtype_id,value) values (@rule_id,@listtype_id,@values) end

you need create split function similar this:

create function [dbo].[split](@string varchar(max), @delimiter char(1)) returns @temptable table (items varchar(max)) begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@string)<1 or @string null homecoming while @idx!= 0 begin set @idx = charindex(@delimiter,@string) if @idx!=0 set @slice = left(@string,@idx - 1) else set @slice = @string if(len(@slice)>0) insert @temptable(items) values(@slice) set @string = right(@string,len(@string) - @idx) if len(@string) = 0 break end homecoming end;

then in stored procedure, phone call function split string:

alter procedure [dbo].[spinsertdistributionrulelisttype] ( @rule_id int, @listtype_id int, @values varchar(max)=null ) begin insert distributionrule_x_listtype (rule_id, listtype_id, value) select @rule_id, @listtype_id, items [dbo].[split] (@values, ',') -- phone call split function end

when execute stored procedure, split values , insert multiple rows table:

exec spinsertdistributionrulelisttype 1, 2, '319,400,521,8465,2013';

see sql fiddle demo. insert next result:

| rule_id | listtype_id | value | --------------------------------- | 1 | 1 | 10 | | 1 | 2 | 319 | | 1 | 2 | 400 | | 1 | 2 | 521 | | 1 | 2 | 8465 | | 1 | 2 | 2013 |

sql sql-server sql-server-2005