Post reply

Unmerging Merged Records

  • February 17, 2004 at 9:49 pm

    #84285

    Hi,

    I've been given the job of sorting 15,000 records that have been merged from two sources with different information.

    The problem is that I need to take whatever information is in either database and use that and ignore the other and I have no idea how to write this query.

    An example of the data would be:

    ORIG_DB

    Data 1

    Data 2

    15000XYZ
    25000 
    15001 
    25001ABC

    I need to end up with a situation where (in the above example) I have one record where Data 1 = 5000 and Data 2 = 'XYZ' and another record where Data 1 = 5001 and Data 2 = 'ABC'. There will also be information that only appears in one database or the other that will need to be included also

    There is no logic to which record has the additional information.

    Any help at the code or at least some suggestions as to the strategy would be greatly appreciated.

    Thanks

    Brett

  • February 18, 2004 at 3:42 pm

    #494600

    What are you to do if you have a record that looks like this: 1,5000,XYZ and another that looks like: 2,5000,UVW ? Would you populate Data2 with 'XYZ' or 'UVW'? (i.e Which database is the master in this situation?). If you can get an answer to this question, I can probably help you out. - apf

  • February 18, 2004 at 4:02 pm

    #494601

    Hi apf,

    Assume that DB1 is the master and that the records from DB2 have to be merged. Is this what you need?

    Thanks

    Brett

  • February 18, 2004 at 4:36 pm

    #494606

    This solution is tailored to the exact situation that you described, the key move is the 'ignore_dup_key' on the index: create table MergedTable (Orig_DB char(1), Data1 int, Data2 char(3)) create unique index AK1Index on MergedTable (Data1) with ignore_dup_key insert into MergedTable select '1', Data1, Data2 from DB1..Table1 where Data2 != ' ' insert into MergedTable select '2', Data1, Data2 from DB2..Table1 where Data2 != ' ' insert into MergedTable select '1', Data1, Data2 from DB1..Table1 insert into MergedTable select '2', Data1, Data2 from DB2..Table1 - apf (I apologize for the poorly formated post - The "Message" input box is just one long line on my browser and I haven't taken the time to figure out why)

  • February 18, 2004 at 5:19 pm

    #494614

    Thanks,

    I'll try that and let you know how I go.

    Cheers

    Brett

Viewing 5 posts - 1 through 4 (of 4 total)

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