How to create a table using "With" clause in SQL

I'm attempting to create a persistent table using the WITH clause however, I'm getting an error.

For context the answer that I currently find is

CREATE TABLE my_table AS WITH my_tables_data AS ( SELECT another_table.data1 AS some_value FROM another_table ) SELECT * FROM some_data; 

However, I am getting an error

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.

My code is

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; ) 

Would anyone be able to provide some guidance on this?

Many thanks in advance!

2 Answers

This is not valid syntax for sql server. you can either create a table using CREATE TABLE and specifying the column names and types, or you can do a SELECT INTO statement including data.

Approach 1 : Create the table and then populate:

CREATE TABLE SalesOrdersPerYear ( SalesPersonID int, BaseSalary float) ; WITH Sales_CTE (SalesPersonID, BaseSalary) AS ( SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY FROM SALES_PERSON WHERE SALES_PERSON_ID IS NOT NULL ) insert into SalesOrdersPerYear SELECT SalesPersonID, BaseSalary AS TotalSales FROM Sales_CTE ORDER BY SalesPersonID, BaseSalary; 

Approach 2 - all in one step

WITH Sales_CTE (SalesPersonID, BaseSalary) AS ( SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY FROM SALES_PERSON WHERE SALES_PERSON_ID IS NOT NULL ) select SalesPersonID, BaseSalary AS TotalSales into SalesOrdersPerYear FROM Sales_CTE ORDER BY SalesPersonID, BaseSalary; 

Use approach 1 when you need to specify more about the table (primary keys, indexes, foregin keys etc.

Use approach 2 for things that are more temporary. (you would normally use a temporary table such as #SalesOrdersPerYear here).

Either way, the data is now stored in your table, and you can use it again.

Using temporary tables:

-- Check for existence and drop first to avoid errors if it already exists. if OBJECT_ID('tempdb..#SalesOrdersPerYear') is not null drop table #SalesOrdersPerYear WITH Sales_CTE (SalesPersonID, BaseSalary) AS ( SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY FROM SALES_PERSON WHERE SALES_PERSON_ID IS NOT NULL ) select SalesPersonID, BaseSalary AS TotalSales into #SalesOrdersPerYear FROM Sales_CTE ORDER BY SalesPersonID, BaseSalary; 

You could also define it as a table variable, which is a bit of a cross between the approaches:

declare @SalesOrdersPerYear table ( SalesPersonID int, BaseSalary float) ; WITH Sales_CTE (SalesPersonID, BaseSalary) AS ( SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY FROM SALES_PERSON WHERE SALES_PERSON_ID IS NOT NULL ) insert into @SalesOrdersPerYear SELECT SalesPersonID, BaseSalary AS TotalSales FROM Sales_CTE ORDER BY SalesPersonID, BaseSalary; 

This option will only persist with this batch, and does not need dropping - just like any other variable.

CREATE VIEW AS my_view AS WITH my_tables_data AS ( SELECT another_table.data1 AS some_value FROM another_table ) SELECT * FROM some_data; CREATE TABLE my_table AS SELECT * FROM my_view;