title: SQL Server CTE implementation
body: <p>I'm writing a script as below based on my business logic, Please could any one suggest me, how can implement CTE (Common table expression)  </p>
  
  <pre><code>IF OBJECT_ID('tempdb..#tmpFirstTable') IS NOT NULL
  DROP TABLE #tmpFirstTable
  
  ;WITH tempCTE AS ( select * from Table1  )      
  
  IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
  DROP TABLE #tmpTable
  SELECT  * 
  into #tmpTable
  from table2  
  
  Select * from tempCTE T1,#tmpTable T2 , #tmpFirstTable T3 where T1.ID = T2.ID and T2.ID = T3.ID
  </code></pre>
  
  <p>The above code format is should be as it is.  </p>
  
  <p>The <strong>ERROR</strong> which I'm getting is.  </p>
  
  <blockquote>
    <p><em>Incorrect syntax near the keyword 'IF'.</em></p>
  </blockquote>