Inserting data into a system-period temporal table

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

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

In this example, the following data was inserted on January 31, 2015 (2015-01-31) into the table created in the example in Creating a system-period temporal table.

Insert three rows into the POLICY_INFO 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);

The POLICY_INFO table now contains the insurance coverage data in the following table. The SYS_START, SYS_END, and TS_ID column entries were generated by the database manager.

Table 1. Data in the system-period temporal table, `POLICY_INFO`, after the INSERT statements
`POLICY_ID`	`COVERAGE`	`SYS_START`	`SYS_END`	`TS_ID`
A123	12000	2015-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2015-01-31-22.31.33.495925000000
B345	18000	2015-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2015-01-31-22.31.33.495925000000
C567	20000	2015-01-31-22.31.33.495925000000	9999-12-30-00.00.00.000000000000	2015-01-31-22.31.33.495925000000

The SYS_START timestamp values are the same for all three newly inserted rows because in this example the rows were inserted as part of the same transaction.

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

The row-begin column, SYS_START, represents the time when the row 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 most cases, the timestamp values for both these columns are the same because they result from the execution of the same transaction. If the transaction start-ID column is defined to allow the NULL value, it is NULL when the value of the transaction start-ID column is identical to the row-begin column.

When multiple transactions are updating the same row, timestamp conflicts can occur. The database manager can resolve these conflicts by adjusting the row-begin column timestamp values. In such cases, the values in the row-begin column and the transaction start-ID column differs. The System-period temporal table timestamp value conflicts topic provides more details on timestamp adjustments.