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




Related Articles :

  • No Related Articles Found

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |