temporary table question

Database

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

2 REPLIES

Re: temporary table question

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

Senior Apprentice

Re: temporary table question

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