Error when inserting CTE table values into physical table

Question

I have a complex query that creates a master CTE_Table form other CTE_Tables. I want to insert the results of the master CTE_Table into a physical table. I'm using Teradata version 15.10.04.03

SELECT Failed. [3707] Syntax error, expected something like a 'SELECT' keyword or '(' or a 'TRANSACTIONTIME' keyword or a 'VALIDTIME' keyword between ')' and the 'INSERT' keyword.

DROP TABLE dbname.physicalTablename ;

CREATE MULTISET TABLE dbname.physicalTablename , NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( col1 INTEGER, col2 INTEGER, col3 INTEGER ) NO PRIMARY INDEX ;

WITH

cteTable3 AS ( SELECT A.colA, A.colB, A.colC, B.col1, B.col2, B.col3 FROM cteTable1 A INNER JOIN cteTable2 ON (blah blah blah) ),

cteTable2 AS ( SELECT col1, col2, col3 FROM SourceTableB ),

cteTable1 AS ( SELECT colA, colB, colC FROM SourceTableA )

INSERT INTO dbname.physicalTablename ( col1, col2, col3, col4, col5, col6 )
SELECT (C3.colA, C3.colB, C3.colC, C3.col1, C3.col2, C3.col3) FROM cteTable3 C3 ;

I don't see an INSERT keyword in your code. Did you share the wrong thing? — JNevill

JNevill JNevill · Accepted Answer · 2018-01-17T16:41:53

While you are missing the INSERT portion of the question, I think the following might clear things up. The correct format for using a CTE in an INSERT is:

INSERT INTO <tablename>
WITH <cte> AS (SELECT...)
SELECT <fields> FROM <cte>

Consider the following:

CREATE MULTISET VOLATILE TABLE tmp AS (SELECT 'bobby' as firstname) WITH DATA ON COMMIT PRESERVE ROWS;

INSERT INTO tmp
WITH cte AS (select 'carol' as firstname)
SELECT * FROM cte;

SELECT * FROM tmp;

DROP TABLE tmp;

Error when inserting CTE table values into physical table

1 Answers