loop through inserts

Question

Post reply

loop through inserts

Peter Turner

Grasshopper

Points: 22
More actions
June 9, 2006 at 4:11 am

#171084

I have to pass a comma seperated string of values to a stored procedure seperate/split them into individual values and insert them into a table one at a time - like
a,b,c,d,e
loop
insert a
next
Hope this makes sence
Brin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

most sites will include functions which will do this, search through the script libraries

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Peter Turner Grasshopper Points: 22 More actions · Answer 2

Thanks for your reply I will have a hunt for such scripts.

For anyone elses info in my quest to solve this microsoft suggest one method is to pass an array as a parameter to a custom data type built using the new .Net CLR programibility. They provide a sample - search books online "parameter array".

Regards

Brin

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

you prob need something like this

create function fn_StringToTable2(@String varchar(8000),@delimiter char(1))

-- table has an indentity and index for forced ordering and selection

returns @results table(c1 varchar(250),NumKey int identity(1,1) primary key clustered)

as

BEGIN

DECLARE @Phrase varchar(250)

DECLARE @Split int

DECLARE @Size smallint

-- seperate the phase(s) between the passed delimiter

SET @String= @String + @delimiter

WHILE (@String <> '')

BEGIN

-- find out how long the phrase is

SET @Size = LEN(@String)

-- now get the position of the delimiter

SET @Split=PATINDEX('%'+@delimiter+'%',@String)

--- extract the phrase removing any spaces from either end

SET @Phrase = LEFT(@String,@Split-1)

insert into @results(c1) values(rtrim(ltrim(@Phrase)))

SET @String = RIGHT(@String,(@Size-@Split))

END

--endwhile

return

END -- function

call as

select * from dbo. fn_StringToTable2 ( 'the dog,the cat,the bus',',')

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Peter Turner Grasshopper Points: 22 More actions · Answer 4

Thanks Colin for your insite and code. Much appreciated.

Regards

Brin