articles >> developer >> How to Delete SQL Server Database Duplicate ...

How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables

By : David VanDeSompele Jul 06, 2004

Page 3 / 3



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

PRINT @nvchCommand


-- execute the statement.

EXEC sp_executesql @nvchCommand

SELECT @iErrorVar = @@Error

IF @iErrorVar <> 0

BEGIN

RETURN

END

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstName,

@vchLastName,

@vchAddress1

END

CLOSE DelDupe

DEALLOCATE DelDupe

RETURN



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.