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

Question

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!

This really doesn't make any sense, what do you expect this to do? — DavidG, Mar 20 '17 at 15:36
there's no `CREATE TABLE WITH CTE AS(...` in SQL Server. What do you want to do? — Lamak, Mar 20 '17 at 15:36
This looks more like it would be a *view* rather than a table. — Damien_The_Unbeliever, Mar 20 '17 at 15:37
Possible duplicate: http://stackoverflow.com/a/9430333/3270427 — McNets, Mar 20 '17 at 15:39
My intent is to create a persistent table that I can access via another Select statement. Originally I was using WITH statements but they're not persistent beyond the select statement they're being implemented with. Does that help? — user2023068, Mar 20 '17 at 15:40
*How* persistent a table? If you only want it within the current scope, or for the lifetime of the current connection, you'd be looking at table variables or temp tables. — Damien_The_Unbeliever, Mar 20 '17 at 15:47

BeanFrog · Accepted Answer · 2017-03-20T15:54:10.070

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.

Additionally, would you mind elaborating on temporary? I would still need to DELETE this "Approach 2" table yes? — user2023068, Mar 20 '17 at 15:49
You would, but the table is not stored in your database, it is stored in tempdb. You can drop it at the end of your procedure. I'll update. — BeanFrog, Mar 20 '17 at 15:50
It looks like table variables might be what you need - they are right at the bottom of the answer. — BeanFrog, Mar 20 '17 at 15:55
This is great! Many thanks for being so clear and concise with your answer. This has been extremely helpful. — user2023068, Mar 20 '17 at 15:56

Ankur Kumar · Answer 2 · 2023-01-05T06:05:06.923

0

Creating a view using WITH and SELECT :

CREATE VIEW  SCHEMA.DEMO
AS 
WITH STAGING AS ( 
   SELECT col1 as id,col2 as name from tbl1
   UNION ALL
   SELECT col1 as id,col3 as name from tbl2
) 
SELECT distinct id,name
FROM STAGING;

edited Jan 05 '23 at 06:05

answered Jan 05 '23 at 06:01

Ankur Kumar

1
1

score -2 · Answer 3 · edited Dec 05 '19 at 13:58

-2

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;

edited Dec 05 '19 at 13:58

XPD

1,121
1
13
26

answered Dec 05 '19 at 12:35

Ederson Garcia

1
2

1

Hi, please expand on *why* this code answers the question. Code snippets without context are not considered good answers. – Nathan Griffiths Dec 05 '19 at 20:15
1

He can create my_table, creating a view with "With clause" – Ederson Garcia Dec 06 '19 at 13:20

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

3 Answers3