Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Create new table using WITH clause

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 :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

>Solution :

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading