So recently I’ve been tasking with cleaning up an SQL database for a developing project I’ve been working on for EPIC and User Provisioning. I had two end up merging two table sets together to get the data I needed, However, duplicate rows impact the process, So I used the following commands below to filter through the Datasets and find the Duped rows to delete all but just one of the instances.

SELECT     COUNT(*) AS Expr2, Dept, JobCode, RightsItem, RightsValue AS Expr1
FROM         (SELECT     TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity
                       FROM          dbo.AppRights
                       WHERE      (AppName LIKE 'EPIC')) AS derivedtbl_1

GROUP BY Dept, JobCode, RightsItem, RightsValue
HAVING      (COUNT(*) > 1)



SELECT     AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID
FROM         dbo.AppRights
WHERE     (ID IN
                          (SELECT     id AS Expr1
                            FROM          (SELECT     TOP (100) PERCENT AppName, Dept, JobCode, RightsItem, RightsValue, Entity, ID
                                                    FROM          dbo.AppRights AS AppRights_1
                                                    WHERE      (AppName LIKE 'EPIC')
                                                    ORDER BY RightsItem) AS derivedtbl_1
                            GROUP BY Dept, JobCode, RightsItem, RightsValue
                            HAVING      (COUNT(*) > 1)))

WITH g AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY AppName, Dept, JobCode, RightsItem, RightsValue, Entity order by Dept) AS row
    FROM dbo.AppRights) 
delete FROM g
WHERE row > 1

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax