Eliminating Duplicate Rows In SQL SERVER 2000

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jamesd0142
    Contributor
    • Sep 2007
    • 471

    #1

    Eliminating Duplicate Rows In SQL SERVER 2000

    My manager and I where looking at some complex code to eliminate duplicate records in a database table.

    then it hit me how its done easily... so i thought i'd share it...

    In English:
    --------------------------------
    -- Eliminating Duplicate rows --
    --------------------------------
    -- select all into a temp table.
    -- truncate origional table.
    -- select only unique rows back into origional table.

    In Code sql server 2000:
    --------------------------------
    -- Eliminating Duplicate rows --
    --------------------------------
    [code=sql]
    -- select all into a temp table.

    select * into temptable1 from <OrigionalTable >
    -- truncate origional table.
    delete <OrigionalTable >

    -- select only unique rows back inot origional table.
    --this usues the selected columns to see if its unique
    insert into <OrigionalTable > select Distinct <columnName> from TempTable1

    --this uses all columnsto see if its unique
    insert into <OrigionalTable > select Distinct * from TempTable1
    [/code]
  • jeenajos
    New Member
    • Oct 2007
    • 71

    #2
    But wen im having multiple column duplication the above code doesnt works. In that situation wat can i do???

    Regards,
    Jeen

    Comment

    • qhjghz
      New Member
      • Aug 2007
      • 26

      #3
      Originally posted by jeenajos
      But wen im having multiple column duplication the above code doesnt works. In that situation wat can i do???

      Regards,
      Jeen

      DELETE FROM table_name where column_name in
      (select column_name from table_name GROUP BY column_name HAVING COUNT(*)>1)

      Comment

      • Erenst
        New Member
        • Nov 2007
        • 2

        #4
        This is what i've done .. creating an cursor and I've used it many times, hope it can help you.


        --Declare all column names in table

        DECLARE @Column1 decimal(9), @Column2 varchar(50), ect .....

        DECLARE duplicate_curso r CURSOR FOR

        -- select all columns in table bit you must have an count column
        select Column1,Column2 , count(*) Cnt, ect ...
        from tblTable
        group by Column1, Column2
        Having count(*) > 1

        OPEN duplicate_curso r

        FETCH NEXT FROM duplicate_curso r
        INTO @Column1, @Column2, ect ...

        WHILE @@FETCH_STATUS = 0
        BEGIN

        SET @Cnt = @Cnt - 1

        SET ROWCOUNT @Cnt

        DELETE tblTable
        WHERE @Column1 = Column1 AND @Column2 = Column1 AND ect ....


        FETCH NEXT FROM duplicate_curso r
        INTO @Column1, @Column2, ect ...
        END

        CLOSE duplicate_curso r
        DEALLOCATE duplicate_curso r

        -- dont forget to set rowcount to 0
        SET ROWCOUNT 0

        Comment

        • gfajardo
          New Member
          • Nov 2007
          • 4

          #5
          Most easy and fast.
          Aply only for duplicate rows in all columns.
          be carefully with the indexes and foreign key before to drop the original table.

          In Code sql server 2000:

          [code=sql]
          select distinct * into temp_table from <original_table >
          drop <OrigionalTable >
          sp_rename temp_table, original_table
          [/code]

          Comment

          • pinaldave
            New Member
            • Dec 2007
            • 3

            #6
            Following website demonstrates easiest way to delete duplicate records from SQL Server (any version).

            A short query to delete duplicate records from a table with an identity column: keep the MAX(ID) in each group of duplicate columns and remove the rest.


            Regards,

            Comment

            Working...