Professional Web Applications Themes

Delete Millions of Duplicate Records - Microsoft SQL / MS SQL Server

Hi Duane, Use a temporary table to copy the rows you want to keep in, clean out the old table with TRUNCATE, and then copy the rows from the temporary table back into the old table. Sample code: BEGIN TRAN SELECT DISTINCT <column_list> INTO #temp FROM old_table TRUNCATE old_table INSERT INTO old_table(<column_list>) SELECT <column_list> FROM #temp DROP TABLE #temp COMMIT TRAN TRUNCATE is a very quick method to clean out a table. A few minor caveats: - You need to be a member of dbo, ddl_admin or sysadmin role to use TRUNCATE - TRUNCATE won't fire triggers. - You can't ...

Sponsored Links
  1. #1

    Default Re: Delete Millions of Duplicate Records

    Hi Duane,

    Use a temporary table to copy the rows you want to keep in, clean out the
    old table with TRUNCATE, and then copy the rows from the temporary table
    back into the old table.

    Sample code:
    BEGIN TRAN
    SELECT DISTINCT <column_list> INTO #temp FROM old_table
    TRUNCATE old_table
    INSERT INTO old_table(<column_list>)
    SELECT <column_list> FROM #temp
    DROP TABLE #temp
    COMMIT TRAN

    TRUNCATE is a very quick method to clean out a table.

    A few minor caveats:
    - You need to be a member of dbo, ddl_admin or sysadmin role to use TRUNCATE
    - TRUNCATE won't fire triggers.
    - You can't use TRUNCATE on a table that is referenced by other tables via
    foreign keys, but as you don't have a primary key or any indexes on that
    table, that can't be the case here.
    - The transaction prevents other users from accessing the table while you
    run this. Try it out on a backup or test system first for performance, it
    might take a couple of minutes.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Duane Hookom" <com> wrote in message
    news:#phx.gbl... 
    primary 
    space. 
    appending 


    Sponsored Links
    Jacco Guest

  2. #2

    Default Delete Millions of Duplicate Records

    The easiest solution seems to be
    SELECT DISTINCT * INTO #TEMP
    then
    TRUNCATE TableWithDupes
    then
    INSERT INTO TableWithDupes
    SELECT * FROM #TEMP
    then
    Add a primary key

    If you DROP your table, you will loose all permissions and
    dependencies. This may be a safer route. HTH. Good luck!

    --Angel
    -------------------------------
    Every time I loose my mind, I
    wonder if it's really worth finding.

     
    about 17 million are 
    there is no primary 
    duplicates and don't need 
    table is huge and 
    the transaction 
    records. I have deleted 
    running out of log space. 
    would cost $ to 
    about adding an 
    possibly take 
    table and appending 
    on our system in 
    responsible for 
    AngelWPB Guest

  3. #3

    Default Re: Delete Millions of Duplicate Records

    Thanks for the quick response. This looks like it will work in my situation.

    --
    Duane


    Duane Guest

  4. #4

    Default Re: Delete Millions of Duplicate Records

    Thanks for the quick response. This looks like it will work in my situation.

    --
    Duane

    "AngelWPB" <com> wrote in message
    news:08df01c3627b$6bb4db20$gbl... 
    > about 17 million are 
    > there is no primary 
    > duplicates and don't need 
    > table is huge and 
    > the transaction 
    > records. I have deleted 
    > running out of log space. 
    > would cost $ to 
    > about adding an 
    > possibly take 
    > table and appending 
    > on our system in 
    > responsible for [/ref]


    Duane Guest

Similar Threads

  1. Duplicate Records
    By Pankaj in forum MySQL
    Replies: 15
    Last Post: February 8th, 01:24 PM
  2. Duplicate records
    By DuLaus in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: October 25th, 03:20 PM
  3. Return Records and not duplicate them :: Again
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 05:57 PM
  4. Deleting duplicate records
    By Dinesh.T.K in forum FileMaker
    Replies: 3
    Last Post: August 13th, 12:00 AM
  5. Duplicate records?
    By Bridget Eley in forum FileMaker
    Replies: 2
    Last Post: July 28th, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139