Assume that the table is empty and you have added the Fog product with a 150 price on 12th November 2017. Here is the code for this action:
INSERT INTO dbo.Product(ProductId, ProductName, Price) VALUES(1, N'Fog', 150.00) ;-- on 12th November
The state of the current table is as follows:
|
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
|
1 |
Fog |
150 |
12.11.2017 |
31.12.9999 |
The state of the history table is as follows:
| ProductId | ProductName | Price | ValidFrom | ValidTo |
|
|
|
|
|
|
Note that the dates in the ValidFrom and ValidTo columns are displayed in short format for clarity; their actual value is of DATETIME2 data type.
As you can see, after an INSERT into a temporal table, the following transitions occur:
- Current table: A new row has been added with attributes from the INSERT statement, the period start date column is set to the system date and the period end date column is set to the maximum value for the DATETIME2 data type
- History table: Not affected at all