troubleshooting Question

Redshift INSERT INTO SELECT DISTINCT FROM cte

Avatar of ttist25
ttist25 asked on
SQL
4 Comments1 Solution56 ViewsLast Modified:
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


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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros