Latest Revision Data Versioning

Historian Server supports multiple versions of a tag value for a particular timestamp. If a tag value currently exists in history, you can insert another value with the same timestamp and allow the historian to add internal version information so that the order of insertion is preserved.

You insert versioned data into history by specifying LATEST for the value of the wwVersion parameter in a Transact-SQL INSERT statement. You can insert versioned data for both I/O Server tags and non-I/O Server tags. The timestamp can be any time except in the future, relative to the historian.

Latest revision data operations never physically delete or alter already-stored original data. Instead, revision data modifications are performed in a separate data stream allowing the original data to be still retrievable in its intact form.

Historian Server supports viewing of only the original version or the latest version of data. Interim versions are preserved in history but are not exposed through the retrieval layer.

If the values inserted are for a time stamp where no data was stored before, it is considered Original-non-streamed data; however, if there was already a value stored for that particular time stamp, it is considered Non-original streamed data.

About INSERT and UPDATE

Using the Transact-SQL INSERT and UPDATE statements, you can insert or update data in the AnalogHistory, DiscreteHistory, StringHistory, and History tables (Value and QualityDetail only). Historian Server uses the same security defined for SQL Server for inserting and updating data. However, as with a .CSV, you cannot delete any data value from storage. If you are attempting to insert or update values whose time period spans across missing history blocks, the necessary history block(s) are recreated for the duration of the data.

INSERT Query Syntax

An INSERT statement with a VALUES clause is supported if you use the four-part syntax or the view name in place of the four-part name as it is used in Lab12. You can use SQL variables in a four-part query.

The general syntax is:

INSERT [INTO] {table_name | view_name} (column_list) VALUES ({DateTime:
constant | variable},
{TagName: constant | variable},
{Value: constant | variable}
[,
{QualityDetail: constant | variable}]
[, {wwTimeZone: constant | variable}] [, {wwVersion: constant | variable}] ) 

Using the four-part name:

INSERT INSQL.Runtime.dbo.AnalogHistory (DateTime, TagName, Value, QualityDetail, wwVersion)
VALUES
(getdate(), 'MyAnalogTag1', 10, 192, 'REALTIME')

Arguments:

• table_name – Name of the extension table into which you want to insert the data. Valid values are AnalogHistory, DiscreteHistory, StringHistory or History
• view_name – Corresponding view name for an extension table. Valid values are v_AnalogHistory, v_DiscreteHistory, v_StringHistory or v_History.
• column_list – Mandatory columns are DateTime, TagName and Value. QualityDetail, wwTimeZone, and wwVersion are optional columns. If the QualityDetail column is omitted in an INSERT … VALUES statement, a QualityDetail value of 192 (Good) is inserted automatically. If the wwTimeZone column is omitted, the time zone of the server is assumed. The wwVersion column defaults to 'original' for non-I/O Server tags and for I/O Server tags.

The following are examples using the view name and its effects in data versioning:

• INSERT non-versioned data for a time stamp where no data was saved before
• INSERT INTO History (DateTime, TagName, Value, QualityDetail) VALUES ('1990-01-01 12:00:00','M100.TT.PV', 55, 192 )

This value will be saved as original. When querying this time stamp, an original or latest value of 55 will be retrieved. 

• INSERT versioned data for a time stamp where no data was saved before
• INSERT INTO History (DateTime, TagName, Value, QualityDetail, wwVersion) VALUES ('1990-01-01 15:00:00','M100.TT.PV', 55, 192, 'Latest' )

This value will be saved as latest. When querying this time stamp, no original value will be found, only a latest value of 55 will be retrieved.

• INSERT non-versioned data for a time stamp where already data was saved before
• INSERT INTO History (DateTime, TagName, Value, QualityDetail) VALUES ('1990-01-01 12:00:00','M100.TT.PV', 77, 192 )

This value will be saved as original. When querying this time stamp, at least two original values will be retrieved: the already stored value, and 77

• INSERT versioned data for a time stamp where already data was saved before
• INSERT INTO History (DateTime, TagName, Value, QualityDetail, wwVersion) VALUES ('1990-01-01 15:00:00','M100.TT.PV', 77, 192, 'Latest' )

This value will be saved as latest. When querying this time stamp for original value, the already stored value will be found and retrieved. When querying this time stamp for latest value, 77 will be retrieved.