Process input data before saving as record to database
-
Hello, is there some possibility how to process input data before save them as record into table (database).
I am using Kepware OPC datalogger module for saving data from PLC to SQL database (using SQL 2014 studio).
I want to record data from counter, each impulse, but create new table row with each impulse results to very big table.
Could I make some temporary variable in SQL to count signals before I make a record to database? For example count impulses for 15 minutes interval and make only one record in database each 15 minutes with offset value which represents the total number of impulses.
Or some other idea how to do it to avoid too many records in table?
Thank you.
-
One idea would be to have two tables, one main table and one which holds data temporarily.
Push all of the individual transactions to the second table.
Periodically (as frequently as you wish) run a job which aggregates the transactions in the temporary-data table, pushes the aggregated result to your 'main' table and then removes the rows which have been aggregated.
Care will need to be taken to ensure that
- Only those rows which are included in the aggregated result(s) get removed, and
- The addition to the main table and the removal from the temporary-data table are encapsulated in a single transaction.
-
I agree with Phil. I would use a "holding" table to contain the data prior to it being summarized in 15-minute "packets" to be added to the main table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply