Duplicated Data

  • satrigney

    SSC Eights!

    Points: 896

    I have a table and a view that I am working with. The view, is a view of Identity Columns and Phone Numbers, which the phone numbers are unique. I need to pull data from the table, joining to the view on phone number, so that I can get the Identity Column. The problem is, that in my table, there are duplicate phone numbers, so I get 2 records, when I get one. Does anyone know of how I can take care of this?

  • GRN

    Hall of Fame

    Points: 3210

    Do you want to delete duplicate records from the table ?? if yes then whats the table structure

  • satrigney

    SSC Eights!

    Points: 896

    No I don't want to delete the duplicate records. I want to only insert one record into the second table. Here's my tables:

    tblCustomer

    iCustomerID

    sPhoneNum

    sCustomerAcctNum

    tblTMPFileLoad

    sPhoneNum

    sCustomerAcctNum

    sSecondaryPhoneNum

    sSecondaryAddress

    tblAddlInfo

    iAddlInfoID

    iCustomerID

    sSecondaryPhone

    sSecondaryAddress

    After I load tblTMPFileLoad, I need to load tblCustomer, and then load tblAddlInfo. But when I join from tblTMPFileLoad to tblCustomer on sPhoneNum, I get 4 records, for the 1 phone number that is duplicated. I want to only get 2 records for that phone number.

  • GRN

    Hall of Fame

    Points: 3210

    Ok i see what ur trying to do ...tblTMPFileLoad is a temp storage place from which u insert into tblCustomer and now u need the id to insert into tblAddlInfo

    if the phone numbers are being duplicated u need a unique field or a combination of fields which is unique. is sCustomerAcctNum unique?

  • satrigney

    SSC Eights!

    Points: 896

    Yes it is. I will try using that.

    Thanks!

  • Andy Warren

    SSC Guru

    Points: 119676

    Hi Trisha,

    I've got a couple questions a little off topic. The first is - are they your actual field names? I ask because you seem to be using Hungarian notation, which I truly dislike in database design (my opinion!) - could you comment on why you use this method and what you see as the advantages?

    Also, if sCustomerAcctNum is unique, why did you choose not to make it the primarykey?

    Just curiousity on my part!

    Andy

  • satrigney

    SSC Eights!

    Points: 896

    Morning Andy,

    Yes, those are the actual table and field names. The reasons I use Hungarian notation is because I am mandated to by my team lead. I do not use it on my own, as I don't think it really serves a purpose in database design.

    The reason that the Customer Account Number is not the primary key is another thing mandated by my team lead. We are to create an identity column on all tables, and make that our primary key. This is both useless in most scenerios, and I disagree with it, but my arguments are not readily heard.

    There are other things that my team lead has us do, that I don't agree with, and are generally not good database design. But, here, I am just a peon, and to him, my opinion does not matter.

    Hope that satisfies your curiosity!

    -Trisha

  • Andy Warren

    SSC Guru

    Points: 119676

    Hey Trisha,

    Thanks for the follow up. Im not against using identity col for primary keys - in many cases I find it simplifies maintenance. Hungarian though - cant support that, next time you change a data type because you need an int instead of a smallint, your naming scheme is broken and usually too expensive to track down every bit of code (client and server) that uses the old field name.

    Still, sounds like not much you can do about it:-)

    Andy

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply