Professional Web Applications Themes

strip down duplicates - Microsoft SQL / MS SQL Server

I have a table with a field called accounts..I have many duplicate account numbers I would like a command that could extract only one instance of each account number and insert it into a temp table...I have tried distinct but there still remain duplicate entries ? thankyou for any help...

  1. #1

    Default strip down duplicates

    I have a table with a field called accounts..I have many
    duplicate account numbers I would like a command that
    could extract only one instance of each account number and
    insert it into a temp table...I have tried distinct but
    there still remain duplicate entries ?

    thankyou for any help
    shau Guest

  2. #2

    Default Re: strip down duplicates

    Try:

    insert #temp (Accounts)
    select distinct Accounts
    from MyTable

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "shau" <net> wrote in message news:0ab401c35c18$5a08c300$gbl...
    I have a table with a field called accounts..I have many
    duplicate account numbers I would like a command that
    could extract only one instance of each account number and
    insert it into a temp table...I have tried distinct but
    there still remain duplicate entries ?

    thankyou for any help

    Tom Guest

  3. #3

    Default strip down duplicates

    Hi Shau,
    You can get to your solution in the following way too. I
    already checked the code.

    SOLUTION SUGGESTED

    Create a table named AccNo with a field named AccountNum

    Now fill up the table with duplicate account number.

    The following select statement shows the duplicates

    select AccountNum from AccNo

    A001
    B001
    C001
    A001
    D001
    F001
    G001
    H001
    B001
    B001

    Now create a procedure where you are filling a temp table
    with only the unique values of account number taken from
    AccountNum table and selecting the AccountNumber field from
    the temp table.


    CREATE PROCEDURE extract_uniq_accno
    AS
    CREATE TABLE #tmpTable001
    (
    AccountNumber VARCHAR(50),

    )

    INSERT INTO #tmpTable001

    SELECT distinct(AccountNum) from AccNo
    SELECT AccountNumber from #tmpTable001
    GO

    Finally you are running the stored procedure with exec
    command

    exec extract_uniq_accno

    This gives the final result which contains unique account
    numbers

    A001
    B001
    C001
    D001
    F001
    G001
    H001

    Using this stored procedure will be of value when you have
    a very large table or/and you need to use this process at
    repeated intervals.

     
    and 
    avi Guest

Similar Threads

  1. recordset duplicates
    By jedale in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 9th, 05:11 PM
  2. SQL Duplicates
    By Sander Martens in forum MySQL
    Replies: 4
    Last Post: January 24th, 07:50 AM
  3. Eliminating duplicates
    By Dave in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 01:56 PM
  4. counting duplicates
    By Dinesh.T.K in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:52 PM
  5. No Duplicates
    By Bebop & Rocksteady in forum FileMaker
    Replies: 2
    Last Post: June 28th, 11:30 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