SELECT INTO with WITH CLAUSE

Tibor_Mozes

Is it possible use the WITH clause with SELECT INTO? The following syntax doesn't work:

WITH my_query1 AS (
 SELECT * FROM users
)

SELECT
  *
INTO
  my_new_table
FROM
   my_query1;

Adrian_Oprea_1

You are not going to able to this in Vertica. No insert,updates or delete statements are allowed. 

lalinuss

Hi Tibor,

 

Im wondering if you found out a workaround for this issue. I use SELECT INTO  a lot and its quite useful with the WITH Clause.

 

Cheers.

 

 

eli_revach

Hi 

As say before by other members , seems like WITH syntax  it’s not supported syntax  for SELECT INTO .

However  , WITH is a very good syntax  with regard to performances if it can materialized its content  ,  Vertica by default do not materialized  WITH part .

 

Simple workaround can  be , by  simply write your query  using inline view as below :

 

SELECT

  *

INTO

  my_new_table

FROM

   (SELECT * FROM users) as aa ;

 

I hope this is answering the question

 

Thanks