Example: Nontemporal Insert into a Transaction-Time Table - Advanced SQL Engine - Teradata Database

A nontemporal insert is similar to a conventional insert, where the transaction-time column is treated as any other column in the table. You can use a nontemporal insert to insert closed or open rows.

Consider the following transaction-time table:

   CREATE MULTISET TABLE Policy_Types (
      Policy_Name VARCHAR(20),
      Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX (Policy_Name);

The following nontemporal INSERT statements insert rows into Policy_Types, explicitly specifying values for the transaction-time column:

   NONTEMPORAL INSERT INTO Policy_Types
      VALUES ('Premium Automobile', 'AP',
         PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));

   NONTEMPORAL INSERT INTO Policy_Types
      (Policy_Name, Policy_Type, Policy_Duration)
      VALUES ('Basic Homeowner', 'HM',
      PERIOD (TIMESTAMP '2004-01-01 00:00:00.000000', UNTIL_CLOSED));