471,873 Members | 980 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

Insert into multiple rows instead of one comma-delimited list?

Hi, all:

I have a form which lets users choose more than one value for each question.
But how do I insert each value as a separate row in my table (instead of
having the values submitted as a comma-delimited list)?

Thanks for your help.

J
Jul 20 '05 #1
2 16651
J
SQL Server does not support arrays.
Look at this example helps you to solve the problem

CREATE PROCEDURE dbo.FAQ_ListToTable
@cslist VARCHAR(8000),
@tablename SYSNAME AS
BEGIN
DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)

WHILE @cslist <> ''
BEGIN
SET @spot = CHARINDEX(',', @cslist)
IF @spot>0
BEGIN
SET @str = CAST(LEFT(@cslist, @spot-1) AS INT)
SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)
END
ELSE
BEGIN
SET @str = CAST(@cslist AS INT)
SET @cslist = ''
END
SET @sql = 'INSERT INTO '+@tablename+'
VALUES('+CONVERT(VARCHAR(100),@str)+')'
EXEC(@sql)
END
END

CREATE TABLE #vals (id INT)
EXEC FAQ_ListToTable '1,2,3,5,7,8','#vals'
SELECT * FROM #vals
drop proc FAQ_ListToTable
"J Belly" <me@privacy.net> wrote in message
news:bo*************@ID-75963.news.uni-berlin.de...
Hi, all:

I have a form which lets users choose more than one value for each question. But how do I insert each value as a separate row in my table (instead of
having the values submitted as a comma-delimited list)?

Thanks for your help.

J

Jul 20 '05 #2
Insert Into <table>
Select <value1>
UNION
SELECT <Value2>
.....
UNION
SELECT <Value n>

HTH
Roji

"J Belly" <me@privacy.net> wrote in message
news:bo*************@ID-75963.news.uni-berlin.de...
Hi, all:

I have a form which lets users choose more than one value for each question. But how do I insert each value as a separate row in my table (instead of
having the values submitted as a comma-delimited list)?

Thanks for your help.

J

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by RotterdamStudents | last post: by
32 posts views Thread by tshad | last post: by
2 posts views Thread by wombat53 | last post: by
4 posts views Thread by Michel Esber | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.