To create a valid-time table as a copy of an existing nontemporal table, use CREATE TABLE AS (the copy table form of CREATE TABLE). Use the AS clause to specify a temporal query that returns a table with valid time.
Example: Creating a Valid-Time Table as a Copy of a Nontemporal Table
Consider the following nontemporal table:
CREATE TABLE Policy_NT ( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) NOT NULL, Policy_Details CHAR(40) ) PRIMARY INDEX(Policy_ID);
To create a copy of the Policy_NT table as a valid-time table, use a nonsequenced query in the AS clause of CREATE TABLE to specify a valid time period of applicability qualifier on the SELECT. The result is a valid-time table where the period of validity for every row is set to the period of applicability that was used in the query.
CREATE MULTISET TABLE Policy( Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity ) AS ( NONSEQUENCED VALIDTIME PERIOD '(2009-01-01, UNTIL_CHANGED)' SELECT * FROM Policy_NT) WITH DATA PRIMARY INDEX(Policy_ID);
The resulting Policy table has a valid-time column named Validity:
SHOW TABLE Policy; CREATE MULTISET TABLE Policy ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC, Policy_Details CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC, Validity PERIOD(DATE) AS VALIDTIME) PRIMARY INDEX ( Policy_ID );