10

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.

topchef
  • 17,019
  • 8
  • 58
  • 98
Adam
  • 1,967
  • 9
  • 48
  • 75

1 Answers1

35

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.

Rob Paller
  • 7,506
  • 23
  • 23
  • Adam, try accepting an answer, it's not too hard..you have multiple posts hanging around with answers but still open!! – cosmos Jun 18 '13 at 04:51
  • This doesn't make sense to me – why use a volatile table in the first place if you're trying to preserve the rows? Why not just `CREATE TABLE` without `VOLATILE` or `ON COMMIT PRESERVE ROWS`? – blacksite Sep 12 '19 at 13:56
  • If your session is using Teradata mode, the creation of the VOLATILE table is an implicit transaction itself. Therefore, the default DELETE ROWS ON COMMIT will result in the data from the SELECT statement being deleted at the end of the implicit transaction. If your session is using ANSI mode, the data should remain until the first COMMIT statement is encountered. This is explain in the Teradata documentation, Teradata Database SQL Data Definition Language Detailed Topics in Chapter 5. – Rob Paller Sep 12 '19 at 14:09