Insert data through virtual tables

You can insert data into a virtual table that is based on a time series table. You can control whether to allow a new time series, duplicate elements for the same timepoints, which columns in the base table can be updated, and how flexible the INSERT statement can be.

You can add a time series element to an existing time series through a virtual table. You can specify to be able to add a time series element into an existing row that does not have any time series data, or to add a row to the base table.

When you insert an element that has the same timepoint as an existing element, the original element is replaced. You can specify to allow multiple elements with the same timepoint.

If the base table has a primary key, the primary key is used to find the row to update and updates to the base table do not require accurate values for columns that are not part of the primary key.

If the base table does not have a primary key, all columns in the table except the TimeSeries column are used to identify the row to be updated and updates to the base table require accurate values for every column in the base table other than the TimeSeries column. You can only update the values in the TimeSeries column.

You can specify the rules for the INSERT statement and which columns can be updated:

  • You can update only the TimeSeries column, but you can specify NULL as the values for non-primary key columns
  • You can update the TimeSeries column and all other non-primary key columns that do not have null values in the INSERT statement.
  • You can update the TimeSeries column and all other non-primary key columns. You can set columns that do not have NOT NULL constraints to null values.
  • You can update the TimeSeries column and all other non-primary key columns that have NOT NULL constraints. You can specify null values for columns that have NOT NULL constraints.

You can speed data insertion by reducing the amount of logging. If you reduce the amount of logging, INSERT statements must be run in a transaction without other types of SQL statements and the elements that are inserted are not visible until the transaction commits.

Control the rules for inserting data by setting the NewTimeSeries parameter and the TSVTMode parameter in the TSCreateVirtualTab procedure.