Sql variables Write query mapping examples

See the topic Supported Table Formats for more information on table formats.

Narrow table format

field_Ts	field_Name	field_Value	field_Quality
11/01/2019 10h00	Wind	39	192
11/01/2019 10h03	Temperature	22,5	192
11/01/2019 10h05	Humidity	64	192

To exploit this table, a write query similar to the following can be constructed.

INSERT INTO [NarrowTable] ([field_Ts],[field_Name],[field_Value],[field_Quality]) VALUES (?param1,'Temperature',?param2,?param3)

Each ?param must be substituted in a unique SQL Variable© using property substitution.

Parameter	Substitution
?param1	Ts
?param2	Quality
?param3	Value

Wide table format with one timestamp

field_Ts	field_Temp	field_Humidity	field_Wind	...
11/01/2019 10h00	22.5	63	39
11/01/2019 10h03	22.5	64	12
11/01/2019 10h05	22.5	64	17

To exploit this table, a write query similar to the following can be constructed.

INSERT INTO[WideTable] ([field_Ts],[field_Temperature],[field_Humidity],[field_Wind]) VALUES (?param1,?param2,?param3,?param4)

Each ?param must be substituted in a different SQL Variable using property substitution.

Temperature variable

Parameter	Substitution
?param1	Ts
?param2	Value
?param3	Empty
?param4	Empty

Humidity variable

Parameter	Substitution
?param1	Empty
?param2	Empty
?param3	Value
?param4	Empty

Wind variable

Parameter	Substitution
?param1	Empty
?param2	Empty
?param3	Empty
?param4	Value

Wide table format with multiple timestamps

field_Ts	field_TsTemperature	field_ValueTemperature	field_TsHumidity	field_ValueHumidity
11/01/2019 10h00	11/01/2019 10h00	22.5	11/01/2019 10h00	63
11/01/2019 10h01	11/01/2019 10h01	23.0	11/01/2019 10h01	64
11/01/2019 10h02	11/01/2019 10h02	23.5	11/01/2019 10h02	65

To exploit this table, a write query similar to the following can be constructed.

INSERT INTO [WideTable] ([field_Ts], [field_TsTemperature], [field_ValueTemperature], [field_TsHumidity], [field_ValueHumidity]) VALUES (?param1,?param2,?param3,?param4,?param5)

Each ?param must be substituted in a different SQL Variable using property substitution.

Temperature variable

Parameter	Substitution
?param1	TsUtcToLocal(“ts”)
?param2	Ts
?param3	Value
?param4	Empty
?param5	Empty

Humidity variable

Parameter	Substitution
?param1	Empty
?param2	Empty
?param3	Empty
?param4	Ts
?param5	Value

Wide table format without no timestamp

field_BatchId	field_Failed	field_Score	field_Operator
4012	0	965	AD
4013	0	1093	AD
4014	0	85	AD
4015	1	15	AD

To exploit this table, a write query similar to the following can be constructed.

INSERT INTO [WideTable] ([field_BatchId],[field_Failed],[field_Score],[field_Operator]) VALUES (?param1,?param2,?param3,?param4)

Each ?param can be substituted in one or more SQL Variables with variable property or variable values.

Batch variable

Parameter	Substitution
?param1	TextAttr10
?param2	BinAttr05
?param3	Value
?param4	TextAttr11

Fixed table format

field_Day	field_Production
Monday	10005

To exploit this table, a write query similar to the following can be constructed.

UPDATE [FixTable] SET [field_Production]=?param1 WHERE [field_Day]=’Monday’

Each ?param can be substituted in one or more SQL Variables with variable property or variable values.

MondayProduction variable

Parameter	Substitution
?param1	Value