insert using CTEs in snowflake

Question

I want to insert some of the resulting columns into another table.

SELECT
        d_objects."OBJECT_GROUP"  AS "d_objects.object_group",
        (TO_CHAR(TO_DATE(d_dates."DAY" ), 'YYYY-MM-DD')) AS "d_dates.date_date",
        COALESCE(SUM(( f_traffic."MEDIA_CONTENT_START"  ) ), 0) AS "f_traffic.sum_content_media_views",
        COALESCE(SUM(CASE WHEN ( d_platforms."PLATFORM"  ) = 'Desktop' THEN ( f_traffic."MEDIA_START" ) ELSE NULL END), 0) AS media_views_web,
        COALESCE(SUM(CASE WHEN ( d_platforms."PLATFORM"  ) = 'Mobile' THEN ( f_traffic."MEDIA_START" ) ELSE NULL END), 0) AS media_views_mobile,
        COALESCE(SUM(CASE WHEN ( d_platforms."PLATFORM"  ) = 'App' THEN ( f_traffic."MEDIA_START" ) ELSE NULL END), 0) AS media_views_app
    FROM DATA_MART.F_TRAFFIC  AS f_traffic
    INNER JOIN DATA_MART.D_DATES  AS d_dates ON (f_traffic."WH_DATE_ID") = (d_dates."ID")
    INNER JOIN DATA_MART.D_OBJECTS  AS d_objects ON
                 (f_traffic."D_OBJECTS_ID") = (d_objects."ID")
    INNER JOIN DATA_MART.D_PLATFORMS  AS d_platforms ON (f_traffic."D_PLATFORMS_ID") = (d_platforms."ID")
    LEFT JOIN DATA_MART.D_CONTENT_MEDIA  AS d_content_media ON (f_traffic."D_CONTENT_MEDIA_ID")  = (d_content_media."ID")
    WHERE ((f_traffic."MEDIA_TYPE" ) <> 'video : vicki' AND (f_traffic."MEDIA_TYPE" ) <> 'audio' AND (f_traffic."MEDIA_TYPE" ) <> 'trailer' OR (f_traffic."MEDIA_TYPE" ) IS NULL) AND (((( d_dates."DAY"  ) >= ((TO_DATE(DATEADD('day', -28, CURRENT_DATE())))) AND ( d_dates."DAY"  ) < ((TO_DATE(DATEADD('day', 28, DATEADD('day', -28, CURRENT_DATE()))))))) AND (d_objects."OBJECT_GROUP" ) = 'BILD') AND ((d_objects."OBJECT_NAME" ) IN ('BILD', 'SPORT BILD') AND ((d_content_media."ADOBE_CONTENT_TYPE" ) = 'video' AND ((lower(d_content_media."TAXONOMY_LIST") ) NOT LIKE '%vicki%' OR (lower(d_content_media."TAXONOMY_LIST") ) IS NULL)))
    GROUP BY
        (TO_DATE(d_dates."DAY" )),
        1
    ORDER BY
        2 DESC
)

The insert statement could look like this:

INSERT INTO PROD_DWH.FOUNDRY_REPORTING.bild_daily_traffic (D_DATES, SUM_CONTENT_MEDIA_VIEWS, MEDIA_VIEWS_MOBILE, MEDIA_VIEWS_APP, REPORT_DATE, REPORT_ID, KPI_NAME)   

This means I will once again have to select from the previous select statement if i do not want to insert all columns. Hence, i want to do the insert using a CTE but I haven't been able to figure out the correct syntax to do an insert using a CTE.

Answer 1

it should be like this

insert into mytable 
with cte as (above select SQL)
select * from cte

Also while inserting, you can remove order by clause.
Example -

insert into mytable 
with cte as (select '2022-01-01' union select '2022-02-01' )
select * from cte

EDIT :

Replying to your comment, yes you can insert into multiple columns like below example. You can even use different logic for different columns.

insert into mytable (col1,col2, col3)
with cte as (
select '2022-01-01' as col1, 'john' as col2, 500 as col3 union 
select '2022-02-01' as col1, 'jane' as col2, 100 as col3 
)
select * from cte

But if you are looking to insert into two different table, that is not possible.