SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looping Comma separated string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/21/2013 :  21:29:13  Show Profile  Reply with Quote
Hi,

I have a stored proc which has two input parameters.

@id bigint, @string nvarchar(max)

@string is comma delimited ex : "100,200,300,400,500".

I wanted to split this comma delimited string and insert into table "Sample" with same id.

sample input my proc :

1, "100,200,300,400,500"

desired output :

Table Name : sample:


1 100
1 200
1 300
1 400
1 500

i can split the data using function which returns as table.

Is there any way to avoid looping(while loop) of delimited data and insert into "sample" table

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 10/21/2013 :  21:34:16  Show Profile  Reply with Quote
Use this splitter function (in Figure 21) http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 10/22/2013 :  06:01:17  Show Profile  Reply with Quote
Declare @ID1 bigint
set @ID1 = 1

DECLARE @str VARCHAR(2000) ='100,200,300,400,500'

DECLARE @strXML VARCHAR(8000) = '<table><row><col>' + REPLACE(REPLACE(@str,',','</col></row> <row><col>'),',','</col><col>') + '</col></row></table>'

DECLARE @XML XML = CAST(@strXML AS XML)

SELECT @ID1 AS ID,
line.col.value('col[1]', 'varchar(1000)') AS col1
FROM @XML.nodes('/table/row') AS line(col)

veeranjaneyulu
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/22/2013 :  07:25:11  Show Profile  Reply with Quote
thank you james and veera.
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 10/22/2013 :  07:41:57  Show Profile  Reply with Quote
Welcome

veeranjaneyulu
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/22/2013 :  21:29:09  Show Profile  Reply with Quote
I am done with insert using the function DelimitedSplit8k made by jeff moden.


INSERT INTO sample
SELECT @id, Item FROM dbo.DelimitedSplit8K(@string, ',');


I am struck with how can i update the sample table with comma seperated values without using while loop. is there any way please suggest me
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/23/2013 :  07:09:55  Show Profile  Reply with Quote
can anyone please suggest me
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 10/23/2013 :  08:36:10  Show Profile  Reply with Quote
quote:
Originally posted by sqllover

I am done with insert using the function DelimitedSplit8k made by jeff moden.


INSERT INTO sample
SELECT @id, Item FROM dbo.DelimitedSplit8K(@string, ',');


I am struck with how can i update the sample table with comma seperated values without using while loop. is there any way please suggest me

I don't really understand what you are asking. The statement you already have is inserting into the sample table, isn't it? And, it is not using a loop.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/23/2013 :  10:02:16  Show Profile  Reply with Quote
Hi james,

sorry for the confusion.i am done.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000