Prev: Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
Next: join data from multiple tables with no keys
From: Roy Harvey (SQL Server MVP) on 18 Jul 2008 13:52
When TRUNCATE is used inside a transaction, if the transaction is
rolled back the TRUNCATE is rolled back too. So TRUNCATE is safe
within a transaction.

I took a quick look at your code and it appears to be fine, but I did
not test it.

Roy Harvey
Beacon Falls, CT

On Fri, 18 Jul 2008 10:05:35 -0700 (PDT), Igor <igor.binder(a)gmail.com>
wrote:

>Is the statement below always true?
>
>I can use TRUNCATE TABLE as in the example below and I am guaranteed
>that if INSERT fails for some
>reason, table Result will be restored to its initial (before the
>transaction began) state?
>
>In other words, can I rely on the fact that the transaction scope
>prevents the pages from being reused (assuming there are no nested
>transactions)?
>
>BEGIN TRY
> BEGIN TRANSACTION
> TRUNCATE TABLE Result
> INSERT INTO Result
> SELECT * FROM Work
> COMMIT TRANSACTION
>END TRY
>BEGIN CATCH
> IF XACT_STATE() <> 0
> ROLLBACK TRANSACTION;
>END CATCH
>
>
>Thanks,
>
>
>-Igor Binder
>
 | 
Pages: 1
Prev: Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
Next: join data from multiple tables with no keys