Inserting data into a system-period temporal table

For a user, inserting data into a system-period temporal table is similar to inserting data into a regular table.

About this task

When inserting data into a system-period temporal table, the database manager automatically generates the values for the row-begin and row-end timestamp columns. The database manager also generates the transaction start-ID value that uniquely identifies the transaction that is inserting the row.

Procedure

To insert data into a system-period temporal 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 system-period temporal table .

INSERT INTO policy_info(policy_id, coverage) 
   VALUES('A123',12000);

INSERT INTO policy_info(policy_id, coverage)  
   VALUES('B345',18000);

INSERT INTO policy_info(policy_id, coverage)  
   VALUES('C567',20000);

Results

The policy_info table now contains the following insurance coverage data. The sys_start, sys_end, and ts_id column entries were generated by the database manager.

Table 1. Data added to a system-period temporal table (policy_info)
policy_id	coverage	sys_start	sys_end	ts_id
A123	12000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
B345	18000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000
C567	20000	2010-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2010-01-31-22.31.33.495925000000

The his_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	sys_start	sys_end	ts_id

Note: The row-begin column, sys_start, represents the time when the row data became current. The database manager generates this value by using a reading of the system clock at the moment it executes the first data change statement in the transaction that generates the row. The database manager also generates the transaction start-ID column, ts_id, which captures the time when execution started for a transaction that impacts the row. In many cases the timestamp values for both these columns are the same because they result from the execution of the same transaction.

When multiple transactions are updating the same row, timestamp conflicts can occur. The database manager can resolve these conflicts by making adjustments to row-begin column timestamp values. In such cases, the values in row-begin column and transaction start-ID column would differ. The Example section in Updating data in a system-period temporal table provides more details on timestamp adjustments.