CODEWITHSUNDEEP
sqlsql-insertcommon-table-expressionvertica
tjt
tjt

Reputation: 728

Vertica - Work around for using WITH with INSERT statement

I have a SQL query like 

with subtable as (
................
)
select * 
from subtable

I wanted to insert the records from the select statement into a table. looking for something like :

with subtable as (......)
insert into newtable 
select * 
from subtable

However, in Vertica, INSERT cannot be used with the WITH ( CTE) statements.

Is there any work around for this?

Thanks for the help

Upvotes: 3

Views: 3648

Answers (2)

marcothesane
marcothesane

Reputation: 6731

I can only agree to what woot said. In Vertica, the common table expression is supported in the SELECT statement - not in any DML statement. That's also what the ANSI standard intended. If your point is to have the data bit at the top of the script and not "hidden" by the INSERT INTO foo line before, then you can (but still with one line before the WITH clause):

CREATE LOCAL TEMPORARY TABLE foo(id,ts,name,exp) ON COMMIT PRESERVE ROWS AS 
          SELECT 1,'2016-12-13 10:11'::TIMESTAMP,'Moshe',1
UNION ALL SELECT 2,'2016-12-13 12:12'::TIMESTAMP,'Karl' ,2
UNION ALL SELECT 3,'2016-12-13 13:12'::TIMESTAMP,'Karl' ,2
UNION ALL SELECT 4,'2016-12-13 14:09'::TIMESTAMP,'Moshe',2
UNION ALL SELECT 5,'2016-12-13 18:07'::TIMESTAMP,'Karl' ,2
KSAFE 0;

Marco the Sane

Upvotes: 0

woot
woot

Reputation: 7616

with is part of the select. 

insert into newtable 
with subtable as (......) 
select * 
from subtable

Upvotes: 14

Related Questions