multiple Insert in 1 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)

multiple Insert in 1 stored procedure

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-03-27 : 07:21:19

Hi all,

I am inserting some tags, in a table and I want to be able to make a multiple insert in 1 stored procedure.

I will pass a string to the stored procedure, for example "celebration; generic; basic; technology;" and I want to be able to extract each word before the ';' check if it already exists in the table, and if not, insert a new record.

Is it possible to do in 1 single stored procedure?

Thanks very much for your help and time

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 08:07:16

Conditional insert:
If Not Exists
(
Select t
From A
Where t = 'celebration'
)
INSERT INTO A]
([t] )
(Select 'celebration' )

Splitting a string variable:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122581

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:08:15

[code]

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(100)
)
AS
BEGIN
DECLARE @Value varchar(100)

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

--------

declare @str varchar(1000)
set @str='celebration; generic; basic; technology;'

insert into table
select
val
from
dbo.parsevalues(@str) a
where
not exists (select * from yourtable where col=a.val)[/code]

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-03-27 : 08:18:33

thanks guys

will try your code and let you know.

thanks for your help

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-03-31 : 07:08:30

Thanks Sakets, that worked excellently.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:14:50

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-03-31 : 07:50:58

Hi Sakets

I am trying to do something similar with your function, to insert into another table

INSERT INTO HS_Video_Tags
(fk_vidID, fk_tagID)
VALUES
(@fk_vidID, (SELECT val FROM dbo.parsevalues(@TagStrings)))

I am trying to insert multiple tags per video in this query, however getting an error

Subqueries are not allowed in this context. Only scalar expressions are allowed.

I could use a cursor, however I am not sure if there is a better and faster way

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:03:21

change it to this,

INSERT INTO HS_Video_Tags
(fk_vidID, fk_tagID)
SELECT @fk_vidID,val FROM dbo.parsevalues(@TagStrings)

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-03-31 : 08:11:54

Excellent!

Thanks a lot mate

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:30:17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources