Creating Temp Tables

[Christopher Ballenger]

Hello, can someone provide me the syntax for CIS to create a temp table

 

I successfully created a permanent table, but having trouble creating a temp one.

 

Thanks,

Chris

[Jason Tiret]

Hi Chris,

 

Just like physical tables, you can either specify columns or use CREATE TABLE AS SELECT...

Here is the syntax:

 

CREATE[LOCAL]{TEMP | TEMPORARY} TABLE table-name{table-element-commalist}[ ON COMMIT {PRESERVE | DELETE} ROWS]

 

or:

 

CREATE[LOCAL]{TEMP | TEMPORARY} TABLE table-nameAS QUERY_EXPRESSION

 

NOTE, this works only in 7.0.4 and above.

 

Regards, Jason

[Christopher Ballenger]

Thank you!

[Pieter Wolthoorn]

Hi Jason, I'm using 7.0.6. When trying

 

CREATE TEMPORARY TABLE temptest AS (SELECT 'test' AS "Test");

 

I get the following error: 

 

Unable to parse SQL script:  Incorrect syntax near "BEGIN", found "CREATE". 

 

Are you sure it works in 7.0.4 and above

[Motsu Aboshi]

I don't think you can do this in an SQL Script, but rather, against a published database from an external db application.

 

Jason can correct me if I'm wrong.

 

Given the age of this post, if you need more help, it might be better to raise a new question.