4
votes

I want to run some updates on a hard table within a transaction, and insert rows into a temp table based on the newly updated hard table, but I don't want to lose the temp table after the transaction is rolled back...is this possible?

I feel like there is an OBVIOUS answer here, and I'm just not seeing it.

1
If it's within a transaction, wouldn't it still rollback the inserts either way? - Chad Baldwin
You can use a table variable, they don't participate in rollback. - Laurence
PERFECT! Thank you. That's just what I needed. - Chad Baldwin

1 Answers

8
votes

Thanks Laurence!!

You can use a table variable, they don't participate in rollback. --Laurence

This was my test to prove it:

DECLARE @test TABLE (Test INT)
BEGIN TRAN
    INSERT INTO @test SELECT 1
ROLLBACK
SELECT * FROM @test

Worked perfect. Thanks! Now to expand on this