when i issue rollback the temptable is also droped reason plz

Question

when i issue rollback the temptable is also droped reason plz

SQL*

SSCertifiable

Points: 6638
More actions
July 26, 2010 at 10:54 pm

#146314

hi,
BEGIN TRAN
create table #Table (Tableid int identity(1,1), TableName Varchar(500))
INSERT INTO #Table
SELECT 'Tablename'
UNION
SELECT 'Tablename2'
UNION
SELECT 'Tablename3'
ROLLBACK TRAN
select * From #Table
It is giving the error:
Server: Msg 208, Level 16, State 1, Line 16
Invalid object name '#Table'.
my doubt is?
is temp table will be dropped when i issue the rollback? or is there any reason.
As temp table is created in temp database, will it be dropped when rollback
🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

SQL* (7/26/2010)
hi,
BEGIN TRAN
create table #Table (Tableid int identity(1,1), TableName Varchar(500))
INSERT INTO #Table
SELECT 'Tablename'
UNION
SELECT 'Tablename2'
UNION
SELECT 'Tablename3'
ROLLBACK TRAN

Since you are issuing BEGIN TRAN before the table creation, when you ROLL BACK it will drop the table. Move the BEGIN TRAN after the create table, then you will be able to do a SELCT * on the table

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SQL* SSCertifiable Points: 6638 More actions · Answer 2

But the temp table will be created in temp database right,

then how the table dropped with out issuing a drop statement.

when the tempdb will be cleared, that means i have created 10 temp tables, i have not drop the tables explicitly , then how long the table will be available with in a connection.

🙂

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Read the "Temporary Tables" section in this article.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQL* (7/26/2010)
But the temp table will be created in temp database right,

Yes

then how the table dropped with out issuing a drop statement.

Because it was created within a transaction. When a transaction rolls back, all changed made within that transaction must be undone. This is not limited to changes in one database. Hence the temp table is dropped (or, more correctly, the create of the temp table is rolled back)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

William Vach SSCertifiable Points: 5478 More actions · Answer 5

If you need to have your items inserted into the temp table persist outside of the transaction, you should consider using a table variable instead of a temp table. Keep in mind that table variables are very inefficent if you load a large amount of data in them.

polkadot SSChampion Points: 13664 More actions · Answer 6

Does that mean that #temp tables are constantly updated as other are updating the tables that it is referencing?

--Quote me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please post new questions in a new thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass