SQL Bluk / Batch update 1

[DSect]

I've searched but have been unable to find info on this operation:

I have data that looks like this:

Row_ID |  Value
---------------
10     | 8273
15     | 211
23     | 214
28     | 4432
31     | 232

I need to update a table with this info. where the Row_Id corresponds to a Row_ID in table of mine.

FYI: This data is the result of a webform that has a textbox for each row of data, allowing a user to update many rows @ one time. Similar to a "Continous Form" in Access, or even a datasheet or datagrid.

So - I've looked around for batch updates / bulk updates, etc.. To no avail

If someone could tell me what this process would be called, I could search more on it.

Also - I'd like to do this in a stored procedure.

I would know the # of rows that need to be updated, the row_id of the record(s) to be updated and the value that the rows would be updated with.

I just cannot fathom the SQL needed to do this.. Possibly dynamic SQL built in my web code, but that's about the only thing I can imagine how to do it, right now.

Thanks as always!!

 

[mrdenny]

I'd create a temp table, put the data into the temp table then do an update table from temp_table to update your data.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DSect]

mrdenny -

That's a good idea.

My question would be, would you do it in an SP?

If you did, how do you get the values to the stored proc?

For instance, I can create a CSV list from an array, etc, but I'm not sure how to do it.

I don't need actual code, just some concepts..

Here's one idea:

Pass the SP a CSV list of values and use TSQL to split the values and update that way? Possibly?

Anyways - ANy info and concepts would be helpful.

Thanks!

 

[Pattycake245]

Depending on what type of webapp you are using you could pass 1 rowid and value at a time to a stored proc and do an update and loop through your results on the app side.

Tim

 

[mrdenny]

Pass a CSV list to the proc. Split the list up using SUBSTRING (search the forums on this, there's tons of sample code out there) and load the values into a temp table. Then process from there.

Yes, this is a perfect examploe of something that should be handled within a stored proc.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)