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.

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.

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!