Inserting data into an application-period temporal table

Inserting data into an application-period temporal table is similar to inserting data into a regular table.

About this task

When inserting data into an application-period temporal table, the only special consideration is the need to include the row-begin and row-end columns that capture when the row is valid from the perspective of the associated business applications. This valid period is called the BUSINESS_TIME period. The database manager automatically generates an implicit check constraint that ensures that the begin column of the BUSINESS_TIME period is less than its end column. If a unique constraint or index with BUSINESS_TIME WITHOUT OVERLAPS was created for the table, you must ensure that no BUSINESS_TIME periods overlap.

Procedure

To insert data into an application-period temporal table, use the INSERT statement to add data to the table.
For example, the following data was inserted to the table created in the example in Creating an application-period temporal table topic. 
INSERT INTO policy_info VALUES('A123',12000,'2008-01-01','2008-07-01');

INSERT INTO policy_info VALUES('A123',16000,'2008-07-01','2009-01-01');

INSERT INTO policy_info VALUES('A123',16000,'2008-06-01','2008-08-01');

INSERT INTO policy_info VALUES('B345',18000,'2008-01-01','2009-01-01');

INSERT INTO policy_info VALUES('C567',20000,'2008-01-01','2009-01-01');

Results

There were five INSERT statements issued, but only four rows were added to the table. The second and third INSERT statements are attempting to add rows for policy A123, but their BUSINESS_TIME periods overlap which results in the following:
  • The second insert adds a row for policy_id A123 with a bus_start value of 2008-07-01 and a bus_end value of 2009-01-01.
  • The third insert attempts to add a row for policy_id A123, but it fails because its BUSINESS_TIME period overlaps that of the previous insert. The policy_info table was created with a BUSINESS_TIME WITHOUT OVERLAPS index and the third insert has a bus_end value of 2008-08-01, which is within the time period of the earlier insert.
The begin column of a period is inclusive, while the end column is exclusive, meaning that the row with a bus_end value of 2008-07-01 does not have a BUSINESS_TIME period overlap with the row that contains a bus_start value of 2008-07-01. As a result, the policy_info table now contains the following insurance coverage data:
Table 1. Data added to an application-period temporal table (policy_info)
policy_id coverage bus_start bus_end
A123 12000 2008-01-01 2008-07-01
A123 16000 2008-07-01 2009-01-01
B345 18000 2008-01-01 2009-01-01
C567 20000 2008-01-01 2009-01-01