All Forums Database
b-arkstuff 2 posts Joined 05/16
17 May 2016
temporary table question

Hello, I am sure someone will say  - you should have looked through the forum for answers - well I did.
In SQL Server there are three types of temporary tables
 table variables (1), #tables (2) and ##tables (3)  - I am sure a teradata  developer will know this.
I want to create type 2 that will disappear whent he session closes.
 
My query is wrapped in a cte
 
with cte(a,b,c)
(
  select 'a','b','c'
)
select *
into tempTableType2
from cte
select a,b,
 
it does not have to select into it can be created and then inserted
create atble temptable2(...)
insert into temptable2
select ...
// I tried below and it does not work - please correct and advise also show how to do insert via cte
create volatile table temptable2 (a varchar(10),b varchar(10,c varchar(10))
Thansk
 
 

b-arkstuff 2 posts Joined 05/16
17 May 2016

That's great  - wait 24 hours for a reply, as if I have nothing better to do

dnoeth 4628 posts Joined 11/04
17 May 2016

At least you got 23 more hours to read the manuals :)
does not work is not a very precise error description, you probably didn't specify the COMMIT option correctly.
 
A direct translation of SQL Server's proprietary SELECT INTO syntax:

CREATE VOLATILE TABLE temptable2 AS
(
with cte(a,b,c)
(
  select 'a','b','c'
)
select *
from cte
) WITH DATA
ON COMMIT PRESERVE ROWS

 

Dieter

You must sign in to leave a comment.