Multiple Row Update/Insert

Hi,
I have a a table that is primarily a linking table that contains values for categories
indid int Indicator ID
indtype int Indicator Type can be either 0 or 1
catflagnum int Category Flag Number the number of the Category
catflagvalue int Cat Flag Value The Value for that category
this table can then be updated from a web form.
The Question I have is that can I do this in one statement or do I have to do it one at a time
i.e
The Data set could look something like
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 1 'This value will change
Catflagvalue = 1 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 2 'This value will change
Catflagvalue = 3 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 3 'This value will change
Catflagvalue = 1 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 4 'This value will change
Catflagvalue = 5 'This value will change
A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insert
I am stumped on this one?

I would say pass all the parameters to a stored proc and inside your stored proc you can branch out as :
IF EXISTS ( SELECT * FROM <table>)
BEGIN
-- Record already exists so you need to UPDATE
END
ELSE
BEGIN
-- You need to do an INSERT
END
Is there any particular reason you are trying to stick to one sql stmt ? You can still get all this done in one trip to the database if thats what you are worried about..|||

I have that sort of T-SQL sorted out, that's not a problem.

Basically what I wanted to do as , the requirement states for me to do is ,

There are a load of drop downlist boxes on a Page , that are basically choices the user can make , etc,

then I need to update the record in , when a user clicks a submit button, so I needed to iterate through all the controls and submit thier values to a

I was wondering if I could do it in one trip, i.e Pass all the parameters to a stored proc, and do it in one batch
at the moment I am doing it in a loop that loops through the controls, and submits thier Data One By One, I was wondering If I could do it in one trip as opposed to 12
if that makes sense?

|||

I was wondering if I could do it in one trip, i.e Pass all the parameters to a stored proc, and do it in one batch
at the moment I am doing it in a loop that loops through the controls, and submits thier Data One By One, I was wondering If I could do it in one trip as opposed to 12
if that makes sense?

Yes. It actually depends on your database structure but for the part you posted above I would think you could pass in all the parameters to a stored proc..write out all your logic there and do the insert/update..all in one trip.

|||

Try the link below for sp_executesql it will enable you run more than one statement. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

|||

Thanks for both of those solutions,

I did learn from the sp_executesql , that will be a solutiuon for another part of the same project, thanks for that.

As for the other solution of passing all my Parameters to a stored proc, I don't like this idea, as it would mean I am passing 24 Parameters to my stored proc, and the coding implications for doing that are not cool. There must be a more elegant solution than that.