CODEWITHSUNDEEP
t-sqlsql-server-2014
Scott
Scott

Reputation: 319

There is already an object named '#t1' in the database

I run this (via SSMS) in a database in which I have write access (with tmp schema defined), and it works fine:

DECLARE @i INT=1
WHILE @i < 5
BEGIN
    PRINT @i;

    IF OBJECT_ID('tmp.t1') IS NOT NULL 
    BEGIN
        DROP TABLE tmp.t1;
    END
    SELECT TOP 3 * INTO tmp.t1 FROM sys.all_columns ac

    IF OBJECT_ID('tmp.t1') IS NOT NULL 
    BEGIN
        DROP TABLE tmp.t1;
    END
    SELECT TOP 3 * INTO tmp.t1 FROM sys.all_objects ao

    IF OBJECT_ID('tmp.t1') IS NOT NULL 
    BEGIN
        DROP TABLE tmp.t1;
    END
    SELECT TOP 3 * INTO tmp.t1 FROM sys.all_parameters ap

    SET @i+=1
END

I run this in a database in which I don't have create table access, and it fails during compilation. 

DECLARE @i INT=1
WHILE @i < 5
BEGIN
    PRINT @i;

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
    BEGIN
        DROP TABLE #t1;
    END
    SELECT TOP 3 * INTO #t1 FROM sys.all_columns ac

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
    BEGIN
        DROP TABLE #t1;
    END
    SELECT TOP 3 * INTO #t1 FROM sys.all_objects ao

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
    BEGIN
        DROP TABLE #t1;
    END
    SELECT TOP 3 * INTO #t1 FROM sys.all_parameters ap

    SET @i+=1
END

I'm clearly dropping the table; if anything I'd expect a runtime error but not a compilation error.

Upvotes: 0

Views: 819

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

It's a compiler behaviour where it can only accept temp tables over SELECT INTO to be used once.

  IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
          DROP TABLE #t1;

    SELECT TOP 3 * INTO #t1 FROM sys.all_columns ac

    IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
         DROP TABLE #t1;

    SELECT TOP 3 * INTO #t1 FROM sys.all_objects ao   -- this query prompt the error

what you can do is

IF OBJECT_ID('tempdb..#t1') IS NOT NULL 
      DROP TABLE #t1;

SELECT TOP 3 * INTO #t1 FROM sys.all_columns ac

IF OBJECT_ID('tempdb..#t2') IS NOT NULL 
     DROP TABLE #t2;

SELECT TOP 3 * INTO #t2 FROM sys.all_objects ao

IF OBJECT_ID('tempdb..#t3') IS NOT NULL 
     DROP TABLE #t3;

SELECT TOP 3 * INTO #t3 FROM sys.all_parameters ap

Upvotes: 1

Related Questions