However, what if we have an existing table in our database that we want to convert to a temporal table? Lets take a look at how we do that.
For this example, lets assume that we have the following table that already exists in our database.
To convert this table to a temporal table, it is a two step process. The first step is that we need to add our ValidFrom/ValidTo columns to the table to represent when the row was active in the table. So we can run the following statement to do this.
Here we are adding the two columns needed for when the row is valid and the PERIOD that is required by a temporal table. Some things to note:
- We could name the columns ValidFrom and ValidTo anything that we want to, these are just the names that I chose.
- These columns must be of a DATETIME2 data type. In this case, I am using DATETIME2(3) to go down to millisecond precision.
- We need to provide default values for these columns in order to populate the existing rows on the table. For my ValidFrom I chose 1/1/1900 as a default starting date. The ending date for the rows in ValidTo column must be the maximum date/time value for our data type, so in this case, 12/31/999 at 23:59:99.999.
- Otherwise, the syntax for the columns look much like the syntax for the columns in the CREATE TABLE statement.
Here, we turn on SYSTEM_VERSIONING for the table so the ValidFrom and ValidTo dates will be auto-generated and define the name of the history table to use.
And that is all there is to it. Now, your table has been converted to a temporal table and any changes to your table will be tracked in the history table.