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 (
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
SQL Error  : [Amazon] (500310) Invalid operation: syntax error at or near "INSERT"
I'm stuck. any ideas would be greatly appreciated!