Create new table using WITH clause-CodePudding

Is it possible to create a table based on a query that is using WITH clause?

The query:

with test as (
  select 999 as col1
)
select * from test;

I tried :

select * into newtable from
(
  with test as(
    select 999 as col1
  )
  select * from test
) as newtable

But I get this error:

Msg 319, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause

NOTE: My real query is more complex so I cannot remove the WITH clause.

CodePudding user response：

You can't stuff a CTE inside a subquery like you're trying to do. Rather the insert syntax should be:

with test as (
  select 999 as col1
)

select *
into newtable
from test;