Skip to main content

SELECT INTO with WITH CLAUSE

  • Jun 4, 2014 2:28 AM UTC
  • 3 replies
  • 13 views
Tibor_Mozes
Forum|alt.badge.img+1
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;



    3 replies

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • Jun 4, 2014 9:20 PM UTC
    You are not going to able to this in Vertica. No insert,updates or delete statements are allowed. 
    lalinuss
    • lalinuss
    • New Participant
    • Jan 14, 2016 9:55 PM UTC

    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
    Forum|alt.badge.img+2
    • eli_revach
    • Participating Frequently
    • Jan 18, 2016 3:06 PM UTC

    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  

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement