Inserting into table using multiple common table expressions CTE

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
N/A

Inserting into table using multiple common table expressions CTE

Hi All,

 

Relatively new to Teradata - am trying to insert a row with static (hard-coded) fields into a query result table. I've been able to successfully create 2 CTE tables per below - I just can't figure out the syntax to insert 1 table into the other.  Feels like this is very close to the solution I want - just can't seem to figure out the correct syntax for the insert into. Thanks for you help in advance!

 

with temp_table1 as (

select cola, colb, colc from dbo.table), --desired query table

 

temp_table2 (cola, colb,colc) as ('static_val1' cola, 'static_val2' colb, 'static_val3' colc) --desired static values to insert

 

insert into temp_table1 select * temp_table2;  -- [3707] Syntax error, expected something like a 'SELECT' keyword...

2 REPLIES
N/A

Re: Inserting into table using multiple common table expressions CTE

What are you actually trying to do?

You can't insert rows into a CTE (and you probably don't want to insert it into the base table).

 

You might UNION a row, but Teradata  does not support VALUES and requires a FROM for set operations:

with temp_table1 as (
   select cola, colb, colc from dbo.table
   union all
   select 'static_val1', 'static_val2', 'static_val3' 
   from (select 1 as x) as dt -- this is just a dummy because FROM must be used 
)
select *
from temp_table1

 

N/A

Re: Inserting into table using multiple common table expressions CTE

This worked perfectly!!! Thanks so much!