How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables
– Calculate number of rows to delete for each grouping by subtracting
– 1 from the total count for a given group.
SELECT @iCount = @iCount – 1
SELECT @chCount = CONVERT(char(3),@iCount)
– now build the rowcount and delete statements.
SELECT @nvchCommand = N’SET ROWCOUNT ‘ + @chCount +
‘DELETE IndTest ‘ +
‘ WHERE vchFirstName = ‘ + CHAR(34) + @vchFirstName + CHAR(34) +
‘ AND vchLastName = ‘ + CHAR (34) + @vchLastName + CHAR(34) +
‘ AND vchAddress1 = ‘ + CHAR(34) + @vchAddress1 + CHAR(34)
– print the statement. For your viewing pleasure only.
– execute the statement.
EXEC sp_executesql @nvchCommand
SELECT @iErrorVar = @@Error
IF @iErrorVar <> 0
FETCH NEXT FROM DelDupe INTO @iCount,
About the Author
David VanDeSompele has worked directly with database systems for 11 years. He is currently the senior DBA for a Seattle-based company that provides firewall and VPN solutions.