Purpose
Creates a new temporal table by copying all or a portion of the structure and contents
of an existing temporal table.
Note: System-time tables cannot be source tables for CREATE TABLE ... AS statements.
Syntax
There is no special additional temporal syntax for the CREATE TABLE ... AS statement.
The syntax is identical to that used for nontemporal tables. That syntax is described
fully in SQL Data Definition Language. But note the restrictions listed in the Usage Notes below.
Usage Notes for ANSI Valid-Time Tables
In order to copy the derived VALIDTIME period definition to the new table, you must
use the table-copy form: CREATE TABLE target_table AS source_table rather than CREATE TABLE target_table AS (subquery).
If you specify new column names in the table-copy form of a CREATE TABLE AS statement,
you must specify a name for the valid-time derived period column, which is also copied
from the source valid-time table.
You cannot, and need not, explicitly specify the [AS] VALIDTIME column attribute in
a CREATE TABLE AS statement, even if you specify new column names for the target table.
If WITH DATA is specified in the CREATE TABLE ... AS statement, the valid-time column
values are copied to the target table.
Target tables can be created with column- and table-level temporal constraints. For
tables with valid-time, primary key and unique constraints automatically create system-defined
join indexes (SJIs). These types of constraints can be defined on target tables in
CREATE TABLE ... AS statements only if the WITH NO DATA option is used. (Note that
NONSEQUENCED VALIDTIME primary key and unique constraints act as for nontemporal table,
and create unique secondary indexes, rather than SJIs.)