title: How to check correctly if a temporary table exists in SQL Server 2005?
body: <p>I have a query where I insert some values from a table:</p>
  
  <pre><code>SELECT ID, NAME INTO #tmpTable1
  FROM TableOriginal
  </code></pre>
  
  <p>First execution is fine, if I press F5(Run) in MSSMS (Microsoft Sql Server Management Studio), the error occured:</p>
  
  <blockquote>
    <p><em>Msg 2714, Level 16, State 6, Line 4<br>
    There is already an object named '#tmpTable1' in the database.</em></p>
  </blockquote>
  
  <p>Good. I decided to check before insert data from <code>TableOriginal</code> to <code>#tmpTable1</code> using:</p>
  
  <pre><code>IF OBJECT_ID('tempdb.#tmpTable1') IS NOT NULL  
    DROP TABLE #tmpTable1
  </code></pre>
  
  <p>Not working, the error shows again as above.</p>
  
  <p>I saw in <code>tempdb</code> database the following temporary table name:</p>
  
  <pre><code>dbo.#tmpTable1__________________0000007
  </code></pre>
  
  <p>Why? Every time when create a temporary table (using first query), the table name will be generated automatically in MSSMS ?</p>
  
  <p>How to remove the existing temporary table to do a new table with new values ?</p>