|
|
Posted by Erland Sommarskog on 09/21/05 10:43
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> In that case, you'll have to bite the bullet and use a list of update
> statements. One for each table. Do enclose all update statements in a
> transaction and include error handling, so that you don't end up with
> the new value in half of your tables changed and the old value in the
> other half. (Instead of enclosing all in a transaction, you could take a
> backup before the conversion starts and restore if something goes
> wrong).
Also, one more important thing here. Don't do:
BEGIN TRANSACTION
go
UPDATE tbl
SET ...
IF @@error <> 0
go
UPDATE nexttbl
That is, don't split up the job in several batches, because some errors
causes SQL Server to abort the batch and rollback the transaction. If you
have multiple batches, you could end up having run the latter half, while
the first was rolled back.
I would recommend that you put all this stuff in a stored procedure, rather
than just a script.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|