BI SQL # 146 : SQL Server DBA Scripts : Delete duplicates from any table
Hi Folks,
In this article we are going to cover How to Delete duplicates from any table.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- Input Parameter of SQL Script
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
Delete duplicates from any table.
Description of SQL Script:
Stored procedure in TSQL based on dynamic SQL to delete any duplicate rows in any table.You need to pass table name to @tableName.If you pass 1 to @check the duplicates will be deleted, but if you pass 0 to @check, the duplicates will be displayed.
Input Parameter of SQL Script
@Tablename sysname, @Check int
SQL Script Code
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE NAME = 'deleteDuplicates' AND xtype = 'P' ) DROP PROCEDURE deleteDuplicates GO CREATE PROCEDURE dbo.deleteDuplicates ( @tableName SYSNAME ,@check INT = 0 --- 0 just to see what are duplicates, 1 delete the duplicates. ) AS BEGIN DECLARE @columnsDelimited NVARCHAR(max) ,@dySql NVARCHAR(max) ,@tran INT ,@tempTable SYSNAME SET @tran = 0 SET @columnsDelimited = '' SET @tempTable = 'delteDuplicates' + CAST(@@SPID AS VARCHAR) BEGIN TRY IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE NAME = @tableName ) BEGIN PRINT @tableName + ' not exists in the current database' RETURN - 1 END SELECT @columnsDelimited = @columnsDelimited + CASE LEN( @columnsDelimited) WHEN 0 THEN '' ELSE ',' END + QUOTENAME(c.NAME) FROM sys.columns C JOIN sys.tables T ON C.object_id = T.object_id AND c.is_identity = 0 AND T.NAME = @tableName IF OBJECT_ID(@tempTable) IS NOT NULL BEGIN SET @dySql = 'DROP TABLE ' + @tempTable EXEC (@dySql) END SET @dySql = 'SELECT ROW_NUMBER() OVER(PARTITION BY ' + @columnsDelimited + ' ORDER BY ' + @columnsDelimited + ' ) as row_number_delete, * into ' + @tempTable + ' FROM ' + @tableName EXEC sp_executeSQl @dySql SELECT @dySql IF (@check = 0) BEGIN SET @dySql = 'SELECT * FROM ' + @tempTable + ' WHERE row_number_delete > 1 ' EXEC (@dySql) END IF (@check = 1) BEGIN SET @dySql = 'DELETE ' + @tempTable + ' WHERE row_number_delete > 1' EXEC (@dySql) BEGIN TRAN SET @tran = 1 SET @dySql = 'TRUNCATE TABLE ' + @tableName EXEC (@dySql) SET @dySql = 'INSERT INTO ' + @tableName + ' (' + @columnsDelimited + ') ' + 'SELECT ' + @columnsDelimited + ' FROM ' + @tempTable EXEC (@dySql) COMMIT TRAN END END TRY BEGIN CATCH IF (@tran = 1) ROLLBACK; SELECT 'errored_line ' + CAST(ERROR_LINE() AS VARCHAR) + ' error_msg ' + ERROR_MESSAGE() + ' error_number ' + ERROR_NUMBER() END CATCH; END GO
SQL Script Output Screenshot
User Level to execute
- 400
Hope you will like How to Delete duplicates from any table.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 