In this database, the vendors machine will INSERT multiple rows into the sql table at a single time. Hence, the time stamp is the InsertTime and will be equal to the same value for multiple rows in the sql data table that my queries are querying.

I use InsertTime as my Timestamp.

I also use InsertTime to know what group of rows was inserted together.

I want to make sure I capture every single updated row.

My SQL query looks kind of like the following:

/SQL="SELECT [ValueToBeRecord] AS PI_VALUE, [InsertTime] AS PI_TIMESTAMP, 0 AS PI_STATUS FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles] WHERE [InsertTime] = (SELECT MAX(InsertTime) FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles]) Order By [Timestamp] asc,[ID_tile] asc;"

This query gets me the most recent group of inserted rows from my table pretty well and in the order that I desire.

(about 15 rows as a time, it gets, based on the limited speed of the production machine.)

I have Location2=1, so my query will select multiple rows at a time.

I have Location5=0, so it should not archive the incoming event if the archive already has a value with the same timestamp? (In my case, I use InsertTime as my timestamp)

However, despite location5 being equal to 0, it will archive the same group of rows from the sql data table as it did in the previous scan.

Consequently, I end up with A LOT of duplicate values, especially when the production machine is not running.

If I had to guess, your interface is scanning far more often than you are actually getting new data sets inserted into SQL. Thus, the interface is going to see the same query result set over and over again until it finally scans after a new result comes in. Regardless of the settings on the interface and PI point side to alleviate duplicate data, we can actually re-configure your PI points and SQL query to prevent the duplicate data in the first place.

This interface allows you to create dynamic SQL queries. That is, you can use dynamic placeholders within your SQL query that only get a value at the time the query is run. That value is potentially different each time the query runs. In this case, we would want to redesign your SQL query like this and add the additional placeholder definition afterwards:

/SQL="SELECT [ValueToBeRecord] AS PI_VALUE, [InsertTime] AS PI_TIMESTAMP, 0 AS PI_STATUS FROM [SupervisorDB].[dbo].[View_Liner_S3_tiles] WHERE [InsertTime] > ? Order By [Timestamp] asc,[ID_tile] asc;"P1=TS

The "TS" placeholder definition will use the current snapshot for this tag at the time of the query to replace the "?" within the SQL query. This way, when the query runs, it will only retrieve data with a timestamp newer than what you have already retrieved. Thus, if there is no new data in SQL, no data is retrieved. This way the interface will simply never encounter the previous result sets so there is no duplicate data to be concerned with.

You can find more information on SQL placeholders in RDBMS in the user manual. Here is a relevant link from LiveLibrary: