Insert a delimited string into a table in one statement

[sodakotahusker]

This is probably kind of an off the wall question. I know I can do this by looping through and parsing indivual values but I thought I'd take a stab at an easier solution. If this just won't fly I'll try to write a generic function that I can call whenever I want to parse the contents of a string into a table.

Is it possible to take a delimted string - let us say comma delimited and do a replacement on the commas to substitute whatever delimimter SQL Server uses between rows so that an insert statement will insert each item as a new row? I tried carriage return/ line feed here but that does not work.


declare @string varchar(500)
declare @crlf varchar(10)
set @string = '155,233,543,765'
set @crlf = char(13) + char(10)
set @string = replace(@string,',',@crlf)

create table #temp (id int)
insert into #temp
(id)
select @string
select * from #temp
drop table #temp

 

[SQLDenis]

If you want it split by each value in 1 row you can look at the code here (

http://sqlservercode.blogspot.com/2005/09/split-comma-delimited-string-fast.html)

It use a pivot (number) table to split the string and returns each part as a seperate row

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/