Problem with Temporary tables

Question

Problem with Temporary tables

Stewart Joslyn

SSCertifiable

Points: 6131
More actions
May 25, 2004 at 7:48 am

#161436

I have SQL Server 7 code of the form :
IF
BEGIN
SELECT INTO #TempTable FROMWHERE
END
ELSE
BEGIN
SELECT INTO #TempTable FROMWHERE
END
This gives a compilation error of :
There is already an object named '#TempTable' in the database.
Please advise how I might get around this. Obviously, the queries are much more complex and I need to retain the condition construct if possible.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

The #Table remains active while your SPID is still open. Either drop the table or close the session and re-issue script...

Andy.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

Thanks for the prompt reply.

This is part of a stored procedure but it fails to compile giving this error. The temporary table does not exist. I have tried putting a DROP #TempTable before each SELECT INTO but I still get the same error.

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Seems you can not create the same #Table twice in a script, even if you DROP it in between.

How about something like this (if structure is the same for either)

IF Object_ID('Tempdb..#TempTable') is Not NULL DROP TABLE #TempTable

SELECT ... INTO #TempTable WHERE 1=2 -- Empty Structure

IF BEGIN

INSERT INTO #TempTable SELECT .. WHERE ...

END ELSE BEGIN

INSERT INTO #TempTable SELECT .. WHERE ...

END

Once you understand the BITs, all the pieces come together

jordanac Hall of Fame Points: 3653 More actions · Answer 4

If you designed the script using Query Analyser and that session is still OPEN the table WILL STILL EXSIST....

Andy.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

I have rebooted the PC, restarted Query Analyser, loaded the script and hit the Run button. I still get the same error.

The problem presumably is that the parser sees the temporary table being created in two places and assumes that it already will exist when it hits the second one (not understanding that it is conditional).

Incidentally, the line number given with the error message is the bottom of the script; not any code referencing the temporary table.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 6

ThomasH,

Thanks for your response - your solution works. Sorry, I missed your post at first because another had been added after it.

The speed (and quality) of responses in this forum is amazing. Many thanks to you both.

ThomasH SSCertifiable Points: 7437 More actions · Answer 7

NP, glad it worked for you. I know it seems easier to code the multiple SELECT ... INTOs, but the single SELECT ... INTO ... WHERE 1=2 to create the structure 1st, then INSERT INTO thereafter has another benafit as well..... WHERE 1=2 is pretty instant no matter what the SELECT because the SQL parser realizes there will be no matching records. Since during the duration of the SELECT ... INTO, SQL server will create locks on the table its creating because it has to go through all the records to determine the type & size of the columns, especially if UNIONS are involved. So the WHERE 1=2 makes the talbe quickly with very short lifespan locks on the schema tables, then the INSERT INTO ... SELECT FROM ... just accesses the source and target tables, not the schema tables.

Once you understand the BITs, all the pieces come together