title: How to create a table using "With" clause in SQL
body: <p>I'm attempting to create a persistent table using the WITH clause however, I'm getting an error. </p>
  
  <p>For context the answer that I currently find is </p>
  
  <pre><code>CREATE TABLE my_table
  AS 
  WITH my_tables_data AS ( 
     SELECT another_table.data1 AS some_value 
     FROM another_table
  ) 
  SELECT * 
  FROM some_data; 
  </code></pre>
  
  <p>However, I am getting an error </p>
  
  <blockquote>
    <p>Msg 319, Level 15, State 1, Line 5 Incorrect syntax near the keyword
    'with'. If this statement is a common table expression, an
    xmlnamespaces clause or a change tracking context clause, the previous
    statement must be terminated with a semicolon.</p>
  </blockquote>
  
  <p>My code is </p>
  
  <pre><code>CREATE TABLE SalesOrdersPerYear  
  WITH t1 AS (
      -- Define the CTE expression name and column list.  
      WITH Sales_CTE (SalesPersonID, BaseSalary)  
      AS  
      -- Define the CTE query.  
      (  
          SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
          FROM SALES_PERSON  
          WHERE SALES_PERSON_ID IS NOT NULL  
      )  
      -- Define the outer query referencing the CTE name.  
      SELECT SalesPersonID, BaseSalary AS TotalSales  
      FROM Sales_CTE   
      ORDER BY SalesPersonID, BaseSalary;  
  )
  </code></pre>
  
  <p>Would anyone be able to provide some guidance on this? </p>
  
  <p>Many thanks in advance! </p>