Create Test Data for Temporal Table on SQL Server 2016
In this SQL tutorial, database developers will find a method how to create test data for temporal table (system-versioned table) and history table in SQL Server 2016 database. What makes it difficult to create test data for temporal table is the history table cannot be updated manually which keeps a history of data changes on temporal table. Temporal table query structures select and filter data according to period columns where UTC datetime2 values are generated automatically.
First of all be sure that you download SQL Server 2016 and install SQL vNext on your computer. SQL Server Temporal tables is first introduced with Microsoft's latest data platform software and will not work on other SQL Server versions.
Following SQL script, first creates a databaes and a schema inside the new database. Later on stockPrices named database table is created as a system-versioned SQL Serve 2016 temporal table.
If you have already a test database on your SQL Server 2016 instance, you can use the Create Table script with replacing the schema name with dbo or another schema you want to use. Of course, do not forget to replace the history table name according to your changes.
create database temporal
GO
use temporal;
GO
create schema kodyaz
GO
create table kodyaz.stockPrices (
symbol varchar(5) not null primary key,
pricedate datetime not null,
price money,
SysStartTime datetime2 Generated Always as Row Start Not Null,
SysEndTime datetime2 Generated Always as Row End Not Null,
Period for System_Time (SysStartTime, SysEndTime)
)
with (System_Versioning = ON (History_Table = kodyaz.stockPricesHistory))
create test data for SQL Server 2016 Temporal (System-Versioned) table and History table
After SQL Server 2016 system-versioned table (temporal table) and its history table is created in the database, we can create a script which will populate test data.
First of all, all data should be inserted or updated on temporal database table. History table is maintained by SQL Server Engine.
Following code insert first row in the temporal table.
Then a SQL WHILE loop is executed with a predefined loop counter value.
Each time the WHILE loop cycles, the initially inserted data will be updated once.
Since I want to have time difference between updates to easily query data based on time, I used SQL Waitfor Delay command.
SQL Server Waitfor Delay enables to create time difference between the execution time of the current line of code with the next code line.
Using Waitfor Delay SQL command, I placed 1 minute time difference between each execution of the SQL WHILE loop.
-- first data on main data table (temporal table or system-versioned table)
insert into kodyaz.stockPrices(symbol, pricedate, price) select 'AAPL', getdate(), 129.90
GO
-- update main table data row every minute in a repeating manner
declare @i int = 0 -- counter
while @i < 30
begin
WAITFOR DELAY '00:01:00' -- hh:mi:ss, wait for 1 minute before next update
update kodyaz.stockPrices
set
pricedate = getdate(),
price = price + (case when rand() > rand() then -1 * round(rand(),2) else round(rand(),2) end)
where symbol = 'AAPL'
set @i = @i + 1
end
Other details like updating the price using SQL RAND() function is my solution for random amount either as increase or decrease of the stock price.
Above SQL script will run once but update temporal table data online over a period of time, and system period datetime2 column values on system-versioned and history tables will be according to these update times. So developers will be able to query temporal table data using new constructs easily with more realistic test data at least for datetime values.