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.
| Author |
Topic |
|
requestor
Starting Member
17 Posts |
 Posted - 2007-10-28 : 05:43:58
|
| Hai, in Sql server 2000 database, i want to do the following..from my UI, i will be updating one table(only 3 columns among the table columns), But the number of records will be around 2000 to 2500. So every 200th record, i want to commit the transaction, so that i cannot lose the data..Using query can i achieve this? or do i need to use the simple while loop logic.Pls advise me |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-28 : 06:24:33
|
| [code]BEGIN TRANSACTION... Update 1 ...IF @@ERROR <> 0 GOTO ERROR... Update 2 ...IF @@ERROR <> 0 GOTO ERROR...COMMITGOTO DONEERROR:ROLLBACKDONE:[/code]Kristen |
 |
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-28 : 06:45:56
|
[quote]Originally posted by Kristen
BEGIN TRANSACTION... Update 1 ...IF @@ERROR <> 0 GOTO ERROR... Update 2 ...IF @@ERROR <> 0 GOTO ERROR...COMMITGOTO DONEERROR:ROLLBACKDONE: KristenSorry. im not clear with the example..i dont understand what you r trying to say |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-28 : 08:15:29
|
| Does the update of all them at once take so long you feel the need to break them up? |
|
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-28 : 09:45:55
|
quote:
Originally posted by dataguru1971
Does the update of all them at once take so long you feel the need to break them up?
yes, all the 2000 record update will happen in one go. So for exmapl, if i commit the update after 2000 records, in case there is any problem in 1901 record, then all the 1900 updated rows also roolled back..i dont want that do happen, so i want to split the records in small amount records and update it. Thanks |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-10-28 : 10:11:10
|
| For that few amount of records, there is surely a more efficient way...can you do it one shot with a Case/conditional statement inside the Update?Update [Table]Set [Column] = (Case when ... end), [Column2] = (Case When...end) OR you can update them using a where clause to only apply to part of the record set?Update [Table]Set [Column] = Where {Condition}Just seems to me that for ony 2500 records, the fastest most efficient way is to do an all at once update. What is the 2000 records vs the 1901 records? Do you have a date field to contend with during the update? I am sure there is another, possibly better way? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-28 : 18:31:38
|
| This is a really bad idea. Try updating all at once - faster and less problematic. This completely goes against your stated goal of not wanting to lose data. Have you given any thought to what happens if 'chunk 3' of your update fails? How is your database going to present you with consistent data if you split atomic operations across transactions?Remember: Transactions are defined by business rules, not technology. |
|
|
|
|
|
|
|
|
Commit transaction logic