From: esquel@sommarskog.se   
      
   tshad (tfs@dslextreme.com) writes:   
   > I am trying to drop and reuse a temporary table and I get an error on the   
   > second select/into.   
   >   
   > *******************************************   
   > IF OBJECT_ID('tempDB..#tfs1') IS NOT NULL   
   >  DROP TABLE #tfs1   
   >   
   > SELECT * INTO #tfs1 ...   
   >   
   > ...   
   >   
   > DROP TABLE #tfs   
   >   
   > SELECT * INTO #tfs1   
   > ********************************************   
   >   
   > This gives me an error:   
   >   
   >      There is already an object named '#tfs1' in the database.   
   >   
   > But this seems to be done before actually executing the query.   
   >   
   > Why would that be?   
      
   Way back in the dim and distant past, in 6.5 and previous versions,   
   this procedure would not compile:   
      
      CREATE PROCEDURE bad_sp AS   
      CREATE TABLE #temp (a int NOT NULL)   
      SELECT b FROM #temp   
      
   You could get an error when you tried to create the procedure that b   
   is a non-existing column.   
      
   Today, you don't get this error, because the temp table does not exist   
   when the procedure is created, and then it blows up at run-time. Deferred   
   name resolution, a horrible misfeature.   
      
   But as you can see in SQL 6.5, SQL Server extracted the temp-table   
   definitions, and validated queries with temp tables. As you realise,   
   this would cause a bit of a problem if the procedure would look like   
   this:   
      
      CREATE PROCEDURE even_worse_sp @a int AS   
      IF @a = 1   
         CREATE TABLE #temp (a int NOT NULL)   
      ELSE   
         CREATE TABLE #temp (c int NOT NULL)   
      SELECT c FROM #temp   
      
   How would SQL 6.5 to compile this? It had but one choice: to outlaw, which   
   it did. With the very same error message, you got.   
      
   With SQL 7, deferred name resolution was introduced, and there was nothing   
   in the above that could confuse compilation. The procedure would or would   
   not blow up at run-time.   
      
   But nevertheless, the check and the restriction was retained. Why? An   
   oversight? Inconsistency is after all the hailmark of SQL Server!   
      
   Anyway, use CREATE TABLE instead. Then you only need it once. Use   
   TRUNCATE TABLE to wipe out all rows.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)