CODEWITHSUNDEEP
teradata
Adam
Adam

Reputation: 2455

Teradata Volatile Table Statement is not creating any rows

I want to create table in Teradata. Therefore I am using this syntax:

    CREATE VOLATILE TABLE a AS
    (
        Select * FROM ...
    ) WITH DATA PRIMARY INDEX ( ACCOUNT_ID )
;

The inner SELECT statement results in 4 rows. However, when I run the entire query, the resulting data set does not have any rows. Strange, I know - that`s why I'm writing. Please help. Thanks.

Upvotes: 13

Views: 73337

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

You need to include the ON COMMIT PRESERVE ROWS option with your DDL for the volatile table:

   CREATE VOLATILE TABLE a AS
    (
        Select * FROM ...
    ) WITH DATA 
    PRIMARY INDEX ( ACCOUNT_ID )
    ON COMMIT PRESERVE ROWS;

The default COMMIT mode for volatile (and global temporary) tables is to DELETE the rows at the end of the transaction.

Upvotes: 42

Related Questions