Errors trying to copy data from a view into a table in snowflake-CodePudding

I am new to snowflake and I am trying to copy data from a view into an existing table

the below CTEs does some processes my data but when I try to copy the output view into a table I get Unexpected insert

with LD as(
 
         select "ID",
                 "Value",
                "Set",
                ROW_NUMBER()OVER ( PARTITION BY "ID" order by "Set" desc )  as rownum 
         from "Archive"."Prty" l
         where l."Prty" = 'Log' AND "ID"= 111
   ),
   LD2 as (
    select "ID",
            "Value",
            "Set",
            ROWNUM
    from LD where ROWNUM = 1
     )
 
 ---- copy view into table -------

 
INSERT INTO "v1" ("ID", "Value","Set",ROWNUM )
  SELECT  * FROM LD2

CodePudding user response：

After much research, I discovered that the insert statement should be at the top and select at the bottom

INSERT INTO "v1" ("ID", "Value","Set",ROWNUM )
    
with LD as(
     
             select "ID",
                     "Value",
                    "Set",
                    ROW_NUMBER()OVER ( PARTITION BY "ID" order by "Set" desc )  as rownum 
             from "Archive"."Prty" l
             where l."Prty" = 'Log' AND "ID"= 111
       ),
       LD2 as (
        select "ID",
                "Value",
                "Set",
                ROWNUM
        from LD where ROWNUM = 1
         )

      SELECT  * FROM LD2

CodePudding user response：

You answer is correct the reason is the instruction is

INSERT INTO X SELECT

which is the same as

INSERT INTO X (SELECT)

and the WITH CLAUSE is a pre-fix to a SELECT, thus second form

INSERT INTO X (WITH y SELECT)

You can see this also happen with things like recursive CTE's which expect the first item to be the recursive CTE but if you want a "prior CTE" you end up having the form

WITH cte_a as (
  -- body of cte_a
), cte_b as(
    WITH recursive_cte as(
       -- body of recursive that uses cte_a
    )
    SELECT * FROM recursive_cte
)
SELECT * from cte_b

This is works, is somewhat ugly, but there we are, there are patterns that the parser expects, and they must be confirmed to.