T-SQL, yes please!

How to form a parameterized SQL query to find duplicates in a table.

This post was most recently updated on August 31st, 2021.

2 min read.

So one day, I needed to quickly check if an identity field in a view in a Microsoft SQL Server was actually unique or not. I was running into weird issues with Entity Framework throwing an error somewhat like this:

Store update, insert, or delete statement affected an unexpected number of rows (2)

Or somewhat like this:

Type: DbUpdateConcurrencyException

Store update, insert, or delete statement affected an unexpected number of rows ([row count]). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.

Now what?

Solution

Table of Contents

Okay – I guess you could achieve this really easily. I mean, I had to google it; it was quick. But still.

How to query a SQL database for duplicates?

  1. Figure out your parameters

    This needs to be reusable, so we’ll parameterize it. I figured I needed table name and parameter name, and that’s it. Maybe your stuff is more complicated (like adding exclusions or something).

  2. Declare your variables

    These two are nicely configurable whenever you need to query a different table later on.

    DECLARE @columnName VARCHAR(50);
    SET @columnName = 'Id';
    DECLARE @tableName VARCHAR(50);
    SET @tableName = 'Notifications';

  3. We want to Group By the column and count the rows

    Group by the column name and count the results – something like this:

    DECLARE @query NVARCHAR(200);
    set @query = 'SELECT ' + @paramName + ', COUNT(' + @paramName + ') FROM ' + @tableName + ' GROUP BY ' + @paramName +' HAVING COUNT(' + @paramName + ')>1;'

    Okay, so that’s a bit convoluted, but it’ll all come together nicely in the end, I assure you.

  4. The end result?

    Below is the SQL I came up with:

    DECLARE @paramName VARCHAR(50);
    SET @paramName = 'Id';

    DECLARE @tableName VARCHAR(50);
    SET @tableName = 'MyTable';

    DECLARE @query NVARCHAR(200);
    set @query = 'SELECT ' + @paramName + ', COUNT(' + @paramName + ') FROM ' + @tableName + ' GROUP BY ' + @paramName +' HAVING COUNT(' + @paramName + ')>1;'
    EXEC sp_executesql @query

As a result, you come out with something like this:


Did it work for you? Found something better? Let me know in the comments below!

References

mm
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments