Repeatedly creating and dropping temp table of the same name

Question

I understand what the debugger is doing but I don't really agree with it, as the following should just surely work :

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

Error message is as follows:

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

Please note - I have variables declared that I require throughout the script, i.e. the script is part of a stored procedure and the variables should have a scope across the whole proc.

If you're going to have the same structure for the temp table throughout, why not `TRUNCATE TABLE` instead of `DROP` followed by `CREATE`? — alroc, Dec 30 '14 at 12:17
@alroc fair suggestion. Is it possible without using `GO` or `TRUNCATE` ? — whytheq, Dec 30 '14 at 12:25
That question doesn't make sense. I've suggested that you use `TRUNCATE` but you're asking if it's possible to use `TRUNCATE` without using `TRUNCATE`? What I'm saying is: create the table **once**. If you need to empty & re-fill it, use `TRUNCATE TABLE` instead of `DROP`ping it only to re-create it. — alroc, Dec 30 '14 at 13:45
Why not just use unique temp table names? Is there an explicit need to have them both named #Extract? Also, you are using the terms "script" and "procedure". If this SQL is inside of a stored procedure, that needs to be known in order to answer the question as `GO` cannot be used inside of a proc. — Solomon Rutzky, Dec 30 '14 at 16:06

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

Just Use Go Statement, Which Ensures that Scope of Current Batch Execution

:setvar TotalScope 10
IF OBJECT_ID('tempdb..#extract') IS NOT NULL

 DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );


//what ever Operation what you want to Perform on `#extract`

 GO


// So here `#extract` is not available then You can Create New One Now
select $(TotalScope)


 IF OBJECT_ID('tempdb..#extract') IS NOT NULL

 DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );


   // Again Do what ever Operation what you want to Perform on `#extract`


 GO

  // So here `#extract` is not available then You can Create New One Now

EDIT

You cann't use This type of @TotalScope variables in Between Batch Execution But You need to ON SQL CMD MODE and then You can Use As I Updated Above code

For Reference

Note: As Suggested By alroc Instead Of Creating New Table One it will be better if You Truncate table

edited May 23 '17 at 11:43

Community

1
1

answered Dec 30 '14 at 12:02

Dgan

10,077
1
29
51

what if I have variables declared that I would like to be scoped across the complete script? – whytheq Dec 30 '14 at 12:03
2

What is the use of `GO` here. Why problem is solved when the statements are in different batch. Even `Begin and End` is not needed only `GO` is what needed. – Pரதீப் Dec 30 '14 at 12:05
@NoDisplayName - GO does get rid of the error message, so I think your criticism is a bit harsh. But I would like to have variables available throughout so GO does clash with that requirement (my fault for not including in the OP) – whytheq Dec 30 '14 at 12:07
@whytheq - Dude am not **criticising** here. Just asking for explanation so that i can understand whats going on :). It will be helpfull for future readers – Pரதீப் Dec 30 '14 at 12:11
@NoDisplayName Now Updated – Dgan Dec 30 '14 at 12:19
@Ganesh_Devlekar if we put a variable `DECLARE @TotalScope INT = 1` at the top of this script how will it be available later on, once `GO` has been added? – whytheq Dec 30 '14 at 12:42
@whytheq have you tried it for yourself to find out? It's the best way to learn and you'll get the question answered a lot faster. – alroc Dec 30 '14 at 13:44

SanyTiger · Answer 2 · 2014-12-30T13:14:56.143

Temp tables are not dropped automatically at the end of a query, only when the current connection to the DB is dropped or you explicitly delete them with DROP TABLE #tablename

EDITED: This will work as an SP as long as you run it in loop, this worked for me, try this:

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;

CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;

Without 'GO' in between, the whole thing will be considered as one single script, so when the CREATE statement tries to create a table of the same name, afetr dropping it a moment before, it shows it already exists because it is one single script where DROP and CREATE of the same table cannot go hand in hand as DROP doesn't get into full effect until the statement is executed, which in this case, it being one big statement, CREATE error occures

With 'GO' , it will consider the part of the script up to 'GO' as one single batch and will execute before getting into the query after 'GO'.

So I suggest this again:

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );
GO
IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

thanks - I've added some extra detail to the OP. I have variables that I want to be scoped across the whole script. — whytheq, Dec 30 '14 at 12:09

Repeatedly creating and dropping temp table of the same name

2 Answers2