英文:
insert using CTEs in snowflake
问题
我想要将一些结果列插入另一个表中。
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)
这意味着如果我不想插入所有列，我将再次需要从先前的选择语句中进行选择。因此，我想使用公共表达式 (CTE) 进行插入，但我还没有能够找到使用CTE进行插入的正确语法。
英文:
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.
答案1
得分: 1
应该是这样的
insert into mytable
with cte as (上面选择SQL)
select * from cte
此外，在插入时，可以删除"order by"子句。
示例 -
insert into mytable
with cte as (select '2022-01-01' union select '2022-02-01' )
select * from cte
编辑：
回答您的评论，是的，您可以像下面的示例一样插入多个列。您甚至可以为不同的列使用不同的逻辑。
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
但如果您想要插入到两个不同的表中，那是不可能的。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论