Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server does not allow to create a TEMP table twice in the same batch, even after you drop the first table.

Jan 20 2014 12:00AM by Anoo S Pillai   

In SQL Server, a temporary table with the same name could not be re-created in the same batch, even if you drop and re-create. The following script clarifies the concept. Try by self

CREATE PROCEDURE TempTabTest 
AS 
BEGIN 

-- first set of data
if object_id  ( 'tempdb..#temptab') is not null drop table #temptab;
create table  #temptab( n1 int, n2 int); 
-- some code

-- second set of data
if object_id ('tempdb..#temptab') is not null drop table #temptab;
create table #temptab( n1 int, n2 int);
-- some code 

END

You will get an error message "There is already an object named '#temptab' in the database.". If you are using a permanent table instead of temporary table, it would get compiled.

This is a feature by design.

Read More..   [0 clicks]

Published under: SQL Server Tips · TSQL Tips · DBA Tips ·  · 


Anoo S Pillai
1441 · 0% · 15
5
 
1
 
 
0
Incorrect
 
0
Interesting
 



Submit

6  Comments  

  • So what is the meaning of this design? Why?

    commented on Jan 26 2014 4:46AM
    dishdy
    16 · 10% · 3269
  • Hi dishdy

    Thanks for the response. The entire background is given in the read more link. Please have a look.

    By the way, there is a related question. There are a lot of SQL Server posts that describes how to check the existence of a temporary table in a database before creating it. I was just thinking, where is that going to be relevant ? Your thoughts please..

    Anoo

    commented on Jan 26 2014 7:40AM
    Anoo S Pillai
    1441 · 0% · 15
  • If you run it as normal code (i.e. no stored procedure) in two batches (i.e. with a 'go' in between) then the drop is needed. If you don't code the 'go' then you will get the same error as with your store procedure. I read the links and I guess what makes it a bit complicated for SQL Server to manage these temporary tables is the fact that you have a multi-user environment. But I still don't see the WHY of the BY DESIGN. Cheers.

    commented on Jan 26 2014 12:06PM
    dishdy
    16 · 10% · 3269
  • Ah !!! "The behavior you are seeing is by design" is the response taken from the related connect Bug ID ( by SQL Programmability Team) . It may be a difficult to fix bug that does not meet it's cost/effort from Microsoft's angle. Hence still a feature by design :)

    commented on Jan 26 2014 7:42PM
    Anoo S Pillai
    1441 · 0% · 15
  • same way you cannot declare variable twice:

      declare @flag int = 5
    
      if( @flag = 1) begin
        declare @ttt int = 10
      end
    
      if( @flag = 2) begin
        declare @ttt int = 20
      end
    
    commented on Jan 27 2014 10:31AM
    vadim
    1523 · 0% · 14
  • This comment is waiting for moderation.

    commented on Nov 8 2014 7:54AM
    prvnkmr449
    3160 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SQL Server does not allow to create a TEMP table twice in the same batch, even after you drop the first table." rated 5 out of 5 by 5 readers
SQL Server does not allow to create a TEMP table twice in the same batch, even after you drop the first table. , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]