Temp tables and transactions - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Temp tables and transactions

Author

Topic

Adde
Starting Member

9 Posts

Posted - 2006-01-17 : 08:50:46

If I create a temp table inside a transaction, on rollback, will the system drop the table or do I have to handle this manually?

Thanks in advance,
Adde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 08:56:58

It will be dropped when you rollback it

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-17 : 09:28:05

It will be uncreated.

If you create the temp table before you start the transaction, it will still be there, but data changes inside the transaction will be rolled back.

Declared tables are not affected by transactions.

The code below illustrates all of this:


create table #t1  ( t1 int )
begin transaction
create table #t2  ( t1 int )
declare @t table ( [@t] int )
insert into #t1 select 1
insert into #t2 select 1
insert into @t select 1
rollback
select * from @t
go
select * from #t1
go
select * from #t2
go
drop table #t1

Results:


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

@t          
----------- 
1

(1 row(s) affected)

t1          
----------- 

(0 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#t2'.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 09:32:44

If you create temp table inside transaction, it will be dropped when you rollback transaction


begin transaction
create table #t(i int)
Insert into #t select 23
select * from #t
rollback transaction

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources