CODEWITHSUNDEEP
sqlsql-server
Jonas WS
Jonas WS

Reputation: 95

Recreate temp table with different columns fails

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
)

Upvotes: 0

Views: 368

Answers (5)

KrishnakumarS
KrishnakumarS

Reputation: 124

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

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

Upvotes: 0

Thom A
Thom A

Reputation: 95554

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;

Upvotes: 1

Shikhar Arora
Shikhar Arora

Reputation: 886

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
)

Upvotes: 0

Esat Erkec
Esat Erkec

Reputation: 1724

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
    )

Upvotes: 0

Related Questions