SQL Server temp table re-create

Question

select * 
into #t1 
from testcur;

if object_id('tempdb..#t1') is not null
    drop table #t1;

select * 
into #t1 
from testcur;

Is there a way to ~~use~~ create temp table again? In this case #t1

If you want to reuse it, why are you explicitly `DROP`ing it? Please add some context on what you are trying to achieve here. — Filburt, Nov 27 '20 at 09:51
Are you by any chance looking for the [difference between #temptable and ##TempTable](https://stackoverflow.com/q/21011276/205233)? — Filburt, Nov 27 '20 at 09:54

score 1 · Answer 1 · edited Nov 27 '20 at 10:00

If you are going to use this script in SSMS (SQL Server Management Studio), You'll get error which is actually a compile error:

Msg 2714, Level 16, State 1, Line 12
There is already an object named '#t1' in the database.

SSMS does not understand that you have dropped the table and going to create again so it shows you error despite your query is valid.

To make SSMS comfortable with your script use GO before second attempt to create the table

select * 
into #t1 
from testcur;

if object_id('tempdb..#t1') is not null
    drop table #t1;

GO -- use GO

select * 
into #t1 
from testcur;

1 Answers1