Can I release temp SQL Tables?

10 Mar 2006 5:10 PM

Preacher Man

In Query Analyzer I am creating a Select statement using temp tables. For a
simple example:

Select abc.def into #tmptable from ABCTable.

If I make changes to the query and then rerun it, it tells me that the
object (which is the temp table) already exist in the database. I have to
close my Query Analyzer window and then reopen it.

So my question is this: Is there a command that I can include in the Query
Analyzer window that will release the Temp Table without having to close the
window?

10 Mar 2006 5:16 PM

Immy

DROP TABLE #TABLENAME
Show quote

"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
>
> Select abc.def into #tmptable from ABCTable.
>
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
>
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>

10 Mar 2006 5:21 PM

Aaron Bertrand [SQL Server MVP]

drop table #tmptable

Show quote

"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
>
> Select abc.def into #tmptable from ABCTable.
>
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
>
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>

10 Mar 2006 5:20 PM

Preacher Man

Thanks everyone for the quick response.

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:ejKaNaGRGHA.4920@tk2msftngp13.phx.gbl...
> drop table #tmptable
>
>
>
> "Preacher Man" <nospam> wrote in message
> news:OaUixWGRGHA.4952@TK2MSFTNGP09.phx.gbl...
>> In Query Analyzer I am creating a Select statement using temp tables.
>> For a simple example:
>>
>> Select abc.def into #tmptable from ABCTable.
>>
>> If I make changes to the query and then rerun it, it tells me that the
>> object (which is the temp table) already exist in the database. I have
>> to close my Query Analyzer window and then reopen it.
>>
>> So my question is this: Is there a command that I can include in the
>> Query Analyzer window that will release the Temp Table without having to
>> close the window?
>>
>
>

13 Mar 2006 6:49 PM

Andy Ball

and u can put a check in too :

IF OBJECT_ID ('tempdb..#testtemp') IS NOT NULL
BEGIN
DROP TABLE #testtemp
END
CREATE TABLE #testtemp (numb int)

INSERT #testtemp VALUES (1)
INSERT #testtemp VALUES (1)

Show quote

"Preacher Man" <nospam> wrote in message
news:OaUixWGRGHA.4952@TK2MSFTNGP09.phx.gbl...
> In Query Analyzer I am creating a Select statement using temp tables. For
> a simple example:
>
> Select abc.def into #tmptable from ABCTable.
>
> If I make changes to the query and then rerun it, it tells me that the
> object (which is the temp table) already exist in the database. I have to
> close my Query Analyzer window and then reopen it.
>
> So my question is this: Is there a command that I can include in the
> Query Analyzer window that will release the Temp Table without having to
> close the window?
>