Archive

Archive for July, 2013

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

    image

    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 :

    Link Resource Website

    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

    | FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
%d bloggers like this: