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

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: DbUpdateConcurrencyExceptionStore 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

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 that 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

• https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable
• https://learnsql.com/blog/how-to-find-duplicate-values-in-sql/