If the historic transaction does not create the sql code to create the table and insert it into the database

Created by Jose Fabian Solano, Modified on Tue, 9 May, 2023 at 5:37 PM by Jose Fabian Solano

function _createTableSqlServer(){
  var query = sprintf("IF OBJECT_ID(N'%1$s', N'U') IS NULL BEGIN\
    CREATE TABLE %1$s (\
        \"id\" INT PRIMARY KEY IDENTITY(0, 1),\
        tag NVARCHAR(255) NOT NULL,\
        number_value FLOAT,\
        quality SMALLINT NOT NULL,\
        ts DATETIME2 NOT NULL\
    )\
  END;\r\n", _getTableName())
  return query;
}Insert:function _insertDataSqlServer(){
  var values = [];
  for(var row of $.input){
    var rowValues = "(";
    rowValues += sprintf("'%s',", row.getValue("tag"));
    rowValues += row.getValue("number_value")+",";
    rowValues += row.getValue("quality")+",";
    //Timestamp is created using the ISO String representation of the date in the ts column of the dataset
    rowValues+= sprintf("'%s'", row.getValue("ts").toISOString());
    rowValues += ")";
    values.push(rowValues);
  }
  var query = sprintf("INSERT INTO %s (tag, number_value, quality, ts) VALUES\r\n%s;\r\n", _getTableName(), values.join(",\r\n"));
  return query;
}

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article