INSERT ​.​.​. VALUES syntax

An INSERT statement with a VALUES clause is supported only if you use the four-part syntax. Using an INSERT statement with a VALUES clause requires a pre-defined, existing tag.

Syntax

INSERT [INTO] {table_name | view_name} (column_list)

VALUES ({DateTime: constant | variable},

{TagName: constant | variable},

{Value: constant | variable}

[, {OPCQuality: constant | variable}]

[, {wwTimeZone: constant | variable}]

[, {wwVersion: constant | variable}] )

Using variables in the VALUES clause is permitted only with four-part naming. For more information, see "Using the Four-Part Naming Convention" in Chapter 6, "Data Retrieval Subsystem," in the AVEVA Historian Concepts Guide.

Arguments

table_name

The name of the extension table into which you want to insert the data. Valid values are: AnalogHistory, DiscreteHistory, StringHistory or History.

view_name

The 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. OPCQuality, wwTimeZone, and wwVersion are optional columns. If the OPCQuality column is omitted in an INSERT … VALUES statement, an OPCQuality 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.

Due to a restriction on the vValue (variant) column in Microsoft SQL Server, any string data inserted or updated must be done to the StringHistory table, not the History table.

The column_list parameter, which is optional in a regular SQL INSERT … VALUES statement, is mandatory for the AVEVA Historian INSERT … VALUES syntax.

Examples

The following examples show valid INSERT … VALUES statements using the "four-part" query syntax.

For more information on four-part queries, see "Query Syntax for the AVEVA Historian OLE DB Provider" in Chapter 6, "Data Retrieval Subsystem," in the AVEVA Historian Concepts Guide.

INSERT INSQL.Runtime.dbo.AnalogHistory (DateTime, TagName, Value, OPCQuality)

VALUES ('1999-11-11 16:05:10', 'NonIOTag1', 56, 192)

INSERT INTO INSQL.Runtime.dbo.StringHistory (DateTime, TagName, Value, wwTimeZone, wwVersion)

VALUES ('1999-11-11 16:05:10', 'IOstring1', 'Batch 10', 'Eastern Standard Time', 'latest')

You can also use the view name in place of the four-part name. For example, in the following queries, v_History and v_AnalogHistory are used instead of the four-part name INSQL.Runtime.dbo.History and INSQL.Runtime.dbo.AnalogHistory, respectively.

INSERT v_History (TagName, OPCQuality, Value, DateTime)

VALUES ('NonIOtag1', 192, 56, '1999-11-11 16:05:10')

INSERT INTO v_History (TagName, DateTime, Value, OPCQuality)

SELECT 'ManualReactTemp', DateTime, 32 + Value * 9 / 5, 192 FROM v_AnalogHistory

WHERE TagName = 'ReactTemp'

AND DateTime >= dateadd(mi, -50, getdate())

AND DateTime < dateadd(mi, -10, getdate())

AND wwRetrievalMode = 'Delta'

You can use SQL variables in a four-part query. For example.

DECLARE @Value float

DECLARE @DateTime DateTime

SET @Value = 1.2345

SET @DateTime = DateAdd(Minute, -10, GetDate())

INSERT v_History (DateTime, TagName, Value, OPCQuality)

VALUES (@DateTime, 'NonIOTag1', @Value, 192)