If it concerns you, running multiple updates in one go, construct the query as a select
see if the query returns what you want then convert it to an update
answered Dec 21, 2010 at 07:58 AM
Kev Riley ♦♦
It really depends. Are you updating 500 records with a basic set of data changes? Then @ThomasRushton already has your answer.
Or, do you have a whole bunch of different changes, let's say a unique change for each of the 500 rows? Then, you have some experimentation ahead of you. I've found, within the databases that I manage, that about 100-200 rows is the cut-off between techniques. Less than 200 rows, then 200 individual UPDATE statements wrapped in a transaction is faster. Above 200 rows, then passing the data to a query as an XML data set which I then use to JOIN to the table and perform an update is faster. But I think this is a situation where Your Mileage May Vary.
answered Dec 21, 2010 at 06:42 AM
Grant Fritchey ♦♦
I know I am very late for this train, but I would like to take Grant's answer and elaborate on the points he makes as they make a lot of sense.
The answer really depends on the format of the deltas source. If you actually have the update hardwired such that the name column always gets a value of aliasN then Thomas has provided complete answer. If this is not the case then you can consider a couple of scenarios. I will use the names of the table and columns just as you have them in your question, so the table named dbo.a has 2 columns named objectid int and name varchar(50).
First scenario: the data representing your deltas can be serialized as xml. I will create a sample table and populate it with some dummy data for the sake of completeness:
This completes the setup, and now you can test the proc:
The table has as many records updated as there are nodes in that xml, so the results of the select from it will look like this:
The second scenario is to use the variable of table type and pass it as a parameter to the proc. This option is available because the database in question is SQL Server 2008. I will not discuss it here because there was a related question in June of this year to which I typed the answer which included a complete sample. I am not sure whether it was helpful or not as the answer does not have any upvotes and it has not been accepted, but still, you might want to look at it. Here is the link to this question.
Just my 2 cents
You can do all at once.
I'll rephrase that. SQL Server doesn't mind whether you update one record of one field, or any number of records or fields.
As long as you can create an appropriate WHERE clause to restrict the rows you want to update, and as long as you can define the appropriate values with which to overwrite your existing data, then there's no problem.
If, on the other hand, you want to apply complex logic to work out what fields should be updated with what, then you might need to do a RBAR (that's Row By Agonizing Row, a phrase coined by one of the other chaps here...)
EDIT: Check MS's documentation on the UPDATE statement, as it may help.
Something along these lines should be what you want
Not tested or anything...
answered Dec 21, 2010 at 06:10 AM