Recreate temp table with different columns fails

Question

When writing scripts you often need to change the columns in a temp table. Despite being guarded by drop-table-if-exists statements it fails because the columns have not changed. Why?

An example that shows roughly what I mean, why doesn't this script work? It fails when recreating #t saying "There is already an object named '#t' in the database.".

It feels like SQL server cheats and only empties the table and not dropping it.

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
    drop table #t

create table #t (
    a int
)

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
    drop table #t

create table #t (
    a int
    ,b int
)

Did you try the approach discussed here: https://stackoverflow.com/questions/659051/check-if-a-temporary-table-exists-and-delete-if-it-exists-before-creating-a-temp — Salman A, Oct 09 '19 at 09:57

score 1 · Answer 1 · answered Oct 09 '19 at 10:04

You are trying to create the same object more than once in the same batch. SQL Server sees this, and so generates a compile error (it happens even before the SQL is actually run).

If you must do this in the same batch, you could use sp_executesql to run the statements in a separate batch; avoiding the error. Note I don't use a table prefixxed with # here, as the # table would only persist within the batch of sp_executesql. Considering what the OP is showing us here, however, I would guess that what we have is overly simplified:

USE Sandbox;
GO


IF EXISTS (SELECT *
           FROM sys.sysobjects
           WHERE id = OBJECT_ID('MyTable'))
    DROP TABLE MyTable;

EXEC sp_executesql N'CREATE TABLE MyTable (a int);';

IF EXISTS (SELECT *
           FROM sys.sysobjects
           WHERE id = OBJECT_ID('MyTable'))
    DROP TABLE MyTable;

EXEC sp_executesql N'CREATE TABLE MyTable (a int, b int);';

GO

DROP TABLE MyTable;

You would hope the parser is a bit more intelligent than that, but apparently not... — Jonas WS, Oct 09 '19 at 11:30
It *is* being intelligent, @JonasWS. Clearly trying to create the **same** table in a batch will cause an error. The Parser doesn't **evaluate** the SQL statement prior to the batch being run; it just parses it. Evaluation does not happen until the statement is actually being run. — Thom A, Oct 09 '19 at 11:34

score 1 · Accepted Answer · answered Oct 09 '19 at 10:16

1

This is a parse error. Even when you parse your query (Ctrl + F5) instead of executing, you get this error. This is because there are two create statements with same table name in the same batch and during the name resolution the table already exists, and the second create statement fails.

Either you need two sessions or put a GO command between the statements. Here is a a similar blog post. An interesting find about Temp tables in SQL Server

answered Oct 09 '19 at 10:16

KrishnakumarS

124
2

Thanks for the link. Mostly the problem arises when changing the definition of a table and rerunning the same query (my example was a bit contrived), eg the parser complaining that the new column doesn't exist. Using GO is a not a perfect solution since all variables go out of scope - unless you fit all table management in the very first part of the script. – Jonas WS Oct 09 '19 at 11:28
1

*"Using GO is a not a perfect solution since all variables go out of scope "* Why I recommended `sp_executesql` as the variables won't go out of scope (and, yes, they can be referredto within the "dynamic" statement, as you can parametrise statements inside `sp_executesql`). – Thom A Oct 09 '19 at 11:37

score 0 · Answer 3 · answered Oct 09 '19 at 09:57

Could you add GO statement

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
        drop table #t

    create table #t (
        a int
    )
    GO
    if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
        drop table #t

    create table #t (
        a int
        ,b int
    )

score 0 · Answer 4 · answered Oct 09 '19 at 09:59

This is because when parsing the batch, SQL Server sees that you already have created temp table #t, so creating it again will produce an error. Use a GO Statement to separate the batch

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
    drop table #t

create table #t (
    a int
)

go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
    drop table #t

create table #t (
    a int
    ,b int
)

Michał Turczyn · Answer 5 · 2019-10-09T10:05:09.037

You have to use GO statement before second if.

See SQL Server Utilities Statements - GO

There you can read:

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

Also you can replace your condition

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#t'))
  drop table #t

with

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

EDIT: alternative approach also requires GO

Recreate temp table with different columns fails

5 Answers5