Insert into Multiple Rows using stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert into Multiple Rows using stored procedure

Author

Topic

csri
Starting Member

45 Posts

Posted - 2008-07-17 : 15:06:15

Dear All
I want to Insert values into multiple rows of a table using stored procedure in sql server 2005.
Table name is Response.columns are as follows
(ResponseId int,queryid int ,commands varchar)
commandIds can have more than 1 value.

My stored procedure is
Create procedure InsertCommandvalues(@p_ResponseId int,p_@querId int,
@p_commandlist varchar(200)) as
begin
end
eg.if commands is having three values xx,yy,zz then three rows must be inserted into the table.i.e
ResponseId queryId commands
1 1 xx
2 2 yy
3 3 zz
Can anyone tell me how to do multiple inserts into the table.

Thanks
sri

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 15:15:19

What you need to do is PARSE a CSV (ie split a comma seperated list of values)
Search this forum for "CSV" and "Parsing functions" for many topics on the subject.

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 21:27:42

here are 2 of them.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 05:33:33

[code]CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Create procedure InsertCommandvalues(@p_ResponseId int,p_@querId int,
@p_commandlist varchar(200)) as
begin
insert into yourtable
select @p_ResponseId,@p_querId,Val
FROM dbo.ParseValues(@p_commandlist,',')
end[/code]

csri
Starting Member

45 Posts

Posted - 2008-07-18 : 23:35:18

Hi visakh16
Thank you very much for your response.Really appreciate your sample.My problem solved.

Thanks
sri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 01:25:35

quote:
Originally posted by csri

Hi visakh16
Thank you very much for your response.Really appreciate your sample.My problem solved.

Thanks
sri

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources