Inserting data into a bitemporal table

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

About this task

When inserting data into a bitemporal table, include begin and 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, this ensures that no BUSINESS_TIME periods overlap.

Procedure

To insert data into a bitemporal table, use the INSERT statement to add data to the table.

For example, the following data was inserted on January 31, 2010 (2010-01-31) to the table created in the example in Creating a bitemporal table .

INSERT INTO policy_info(policy_id, coverage, bus_start, bus_end) 
   VALUES('A123',12000,'2008-01-01','2008-07-01');

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

INSERT INTO policy_info(policy_id, coverage, bus_start, bus_end)  
   VALUES('B345',18000,'2008-01-01','2009-01-01');

INSERT INTO policy_info(policy_id, coverage, bus_start, bus_end)  
   VALUES('C567',20000,'2008-01-01','2009-01-01');

Results

The policy_info table now contains the following insurance coverage data. The sys_start, sys_end, and ts_id column entries are generated by the database manager. 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.

Table 1. Data added to a bitemporal table (policy_info)
policy_id	coverage	bus_start	bus_end	sys_start	sys_end	ts_id
A123	12000	2008-01-01	2008-07-01	2010-01-31- 22.31.33. 495925000000	9999-12-30- 00.00.00. 000000000000	2010-01-31- 22.31.33. 495925000000
A123	16000	2008-07-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	9999-12-30- 00.00.00. 000000000000	2010-01-31- 22.31.33. 495925000000
B345	18000	2008-01-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	9999-12-30- 00.00.00 .000000000000	2010-01-31- 22.31.33. 495925000000
C567	20000	2008-01-01	2009-01-01	2010-01-31- 22.31.33. 495925000000	9999-12-30- 00.00.00. 000000000000	2010-01-31- 22.31.33. 495925000000

The hist_policy_info history table remains empty because no history rows are generated by an insert.

Table 2. History table (hist_policy_info) after insert
policy_id	coverage	bus_start	bus_end	sys_start	sys_end	ts_id