Redshift INSERT INTO SELECT DISTINCT FROM cte

ttist25
ttist25 used Ask the Experts™
Hopefully that title explains it well enough but...

I'm working in a Redshift instance and I have a CTE that pulls fields from a couple tables and uses dense_rank to add an id for the grouped rows.  It seems to be working fine because I can select rows from my CTE and the results appear as I would expect.  

Next, I'm trying to insert the distinct rows from that CTE into an existing table.  That's where all hell breaks loose.  Well, maybe not all hell but definitely an error. :)

I'm still a noob w/ Redshift so I'm not sure how to do temp tables or I'd give some DDL here but, here is essentially what the code looks like:

WITH cte_MyCTE AS (
    SELECT 
        tbl1.[MyPID],
        tbl1.[MyDate],
        tbl2.[MyNumber],
        tbl2.[MyOtherNumber],
        DENSE_RANK() OVER(ORDER BY tbl1.[MyPID], tbl1.[MyNumber], DATEADD(HOUR, (DATEDIFF(HOUR, '1900-01-01 00:00:00.000', tbl1.[MyDate]/3)*3, '1900-01-01 00:00:00.000')) AS [MyGrpID]
FROM Table1 AS tbl1
LEFT JOIN Table2 AS tbl2
)
INSERT INTO Table3
SELECT DISTINCT
    [MyGrpID],
    [MyPID],
    [MyNumber],
    [MyOtherNumber]
FROM cte_MyCTE

Open in new window



Redshift says
SQL Error [500310] [421601]: [Amazon] (500310) Invalid operation: syntax error at or near "INSERT"

I'm stuck.  any ideas would be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Try this syntax:
INSERT INTO Table3
WITH cte_MyCTE AS (
    SELECT 
        tbl1.[MyPID],
        tbl1.[MyDate],
        tbl2.[MyNumber],
        tbl2.[MyOtherNumber],
        DENSE_RANK() OVER(ORDER BY tbl1.[MyPID], tbl1.[MyNumber], DATEADD(HOUR, (DATEDIFF(HOUR, '1900-01-01 00:00:00.000', tbl1.[MyDate]/3)*3, '1900-01-01 00:00:00.000')) AS [MyGrpID]
FROM Table1 AS tbl1
LEFT JOIN Table2 AS tbl2
)
SELECT DISTINCT
    [MyGrpID],
    [MyPID],
    [MyNumber],
    [MyOtherNumber]
FROM cte_MyCTE

Open in new window

Author

Commented:
THANK YOU!!!!

Not sure why it worked but it definitely did!

Can you point me to something that explains why?  Either way - NICE JOB!
Most Valuable Expert 2012
Distinguished Expert 2018
Many database products use that syntax.

I'm not familiar with Redshift but if these are the docs:
https://github.com/awsdocs/amazon-redshift-developer-guide/blob/master/doc_source/r_INSERT_30.md

The syntax shows the INSERT first then 'expression'.  The CTE expression is part of the actual select making it part of the expression.

Author

Commented:
Got it!  Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial