How to find duplicate items in IndustrialSQL Server

Tech Note: ISS-TN017
Published Date: May 23, 2007
Primary Product: Historian Server
Revision Date:

Executing query using MS Query Analyzer

In order to execute queries listed below you need to
1. Launch Query Analyzer by selecting Start -> Programs -> Microsoft SQL Server -> Query Analyzer

2. Log into Microsoft SQL Server

3. From the list of databases located on the toolbar select Runtime

4. Type in or paste the query text into the white editor window

5. Press F5 or select Query menu -> Execute to run the query and get results in the lower pane

Determining whether duplicate items exist

To determine whether duplicate items exist execute the following query in Query Analyzer:
SELECT ItemName, MIN(TagName) AS TagName, COUNT(*) AS [Count]
FROM Tag
GROUP BY ItemName
HAVING (COUNT(*) > 1) AND (ItemName > ”)

The query will return the list of duplicate items, number of duplicates (if greater than 1) and one of the tag that contains the repeated item.

Using InSQL Management Console you can locate the tag and review its item configuration.

Determining tagnames that contain duplicate items

Optionally you can execute the following query in Query Analyzer to obtain the list of tag that have the repeated item from the previous query:
SELECT TagName, wwTagKey FROM Tag WHERE (ItemName = ‘‘)

Note: replace with the actual name of the duplicate item.

The query will return the list of tags that have the duplicate item:

Alternative solution

Alternatively both steps described above may be combined into a single query:
SELECT TagName, wwTagKey, ItemName
FROM Tag
WHERE (ItemName IN
(SELECT ItemName
FROM Tag
GROUP BY ItemName
HAVING (COUNT(*) > 1) AND (ItemName > ”)))
ORDER BY ItemName, TagName

The query will return all tagnames, their keys (wwTagKey) and items that are duplicate.


All Industrial Software Solutions Tech Notes are provided "as is" without warranty of any kind.