Post reply

General SQL Development Question.

  • May 10, 2019 at 11:57 am

    #3639787

    Hello Folks,

    We do have one source system (Read Only) where member information is hosted - contains around 100 fields and volume won't be more than 10,000 rows. We would like to build a logic to accomplish below ones. i just need idea on this (best way to design). I can able to write logic.

    1. Source system is ready only one-data can be updated/added once in a day.
    2. First -we need to fetch all data from system - all are new members ,
    3. second -need to check whether member already exist or not , if not it would be new member
    4. third - if member already exists then check for any change in all fields if yes then send changes to system

    So totally 2 files 1 for new and other for change/update. below is the systems

     

    Source(Oracle) --- Middle Ware (SSIS/SQL System-Data host - check new/change) -Generate file - Feed to final system(Destination)

    Hope it's clear, Please let me know any questions.Thanks!

     

     

  • May 10, 2019 at 1:10 pm

    #3639847

    It's known as an "Upsert" or a "Merge".

     

    What IS the final destination here?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 10, 2019 at 1:36 pm

    #3639868

    For 10,000 rows, it could well be faster (and simpler to code and maintain) for you to do a full truncate/reload every time.

    Unless you have a reason not to?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 10, 2019 at 2:00 pm

    #3639910

    koti.raavi wrote:

    second -need to check whether member already exist or not , if not it would be new member 

    Just to clarify, does the source contain the full current data set or just new members?  Also how often do you need to update?  If you only need to update it once a day than I agree with Phil.  Set up a job to run off hours when no one is using the system.  I've done some fun stuff with table switching for large tables that would seriously affect availability even in off hours but I wouldn't worry about that for 10,000 records.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • May 11, 2019 at 1:55 am

    #3640283

    Thanks for responses, here is the answers to questions.

    Jeff -  Destination is unknown, we just need to place file in network location in specific format requested by destination team

    Phil - If I do truncate and load, how do i cross check member existence as we don't have access to destination system. I'm thinking stage is truncate and load - and two detail tables - One for new members and other one for changed one's - any thoughts?

    Y.B - Yes - Source contains all data, it will keep updated based on member request.

    Please let me know any questions, Thank You So Much for help!

     

     

  • May 11, 2019 at 1:44 pm

    #3640461

    koti.raavi wrote:

    Jeff -  Destination is unknown, we just need to place file in network location in specific format requested by destination team.

    There could be a serious advantage in doing this if we knew the destination.  By that I mean, is it an SQL Server, an Oracle Server, a spreadsheet, or ???  You should as the destination team.

     

    At the very least, we need to know what that "specific format" is because even that can help us help you much better.

     

    We also need to know in no uncertain terms, what needs to be in that destination file.  Is it supposed to be just differences between what you peel out of Oracle and what you are maintaining in SQL Server?  In your Step 2 on the original post, it seems like you're saying that everything should be only "new members" because you state "all are new members" and yet, in Step 3, you say you have to check if they're new members or not and in Step 4, you talk about updating existing members.  That's leads us to your response to Y.B. where you state "Yes - source contains all data" but we don't actually know what you mean by "all data" because of what you said in your Step 2.

    So, we need to know for sure...

    1. What is actually contained in the source data from the Oracle system?  Is it truly ALL THE DATA with the full monty of ALL MEMBERS whether they're new or not or is it something less than that?

    2. What needs to actually be in the destination file you're trying to create?  Is it ONLY changes from yesterday's file (Inserts/Updates) that you received or is it something else?

    3. What IS the required format for the destination file?  Is it fixed width fields, true CSV with double quoted text columns, "stupid" CSV where only the text data that has commas in it gets double quoted (the double quotes are also known as text qualifiers), TAB separated, or ???

     

    I guess my other question is why in the heck aren't you folks doing this on the system of record?  In other words, the Oracle system supposedly contains "ALL of the data"... why are you good folks duplicating the Oracle data on an SQL Server to do this instead of getting the folks on the Oracle system to do it?  It's been more than a decade since I've had to work with Oracle but I do remember that it was pretty easy to do such things (and I did a lot of these types of things).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 11, 2019 at 4:41 pm

    #3640524

    Thanks Jeff for making it to clear, below is the answer to questions.

    1. What is actually contained in the source data from the Oracle system?  Is it truly ALL THE DATA with the full monty of ALL MEMBERS whether they're new or not or is it something less than that?

    Let me provide one example, it is member enrollment system-   I have enrolled to one health care plan today and you are enrolled after few days.After couple of months i have updated first name . It means members keep on enrolling into health plan and also they will quit/update whenever they need. Hope it is clear.

    2. What needs to actually be in the destination file you're trying to create?  Is it ONLY changes from yesterday's file (Inserts/Updates) that you received or is it something else?

    Correct  Insert/updates -  Use above example when i enrolled it is insert and any update to my details then it is update.- unfortunately source system doesn't have any update flag  column

    3. What IS the required format for the destination file?  Is it fixed width fields, true CSV with double quoted text columns, "stupid" CSV where only the text data that has commas in it gets double quoted (the double quotes are also known as text qualifiers), TAB separated, or ??

    Currently excel file , but i have suggested csv - pipe delimited- yet to finalize.

    Yes, oracle team can do , but they don't have bandwidth to do :(. The reason why we need maintain data in two system is, company is taken over destination box, once they are fully migrated we can get rid of process.

    • This reply was modified 5 years, 6 months ago by  koti.raavi. Reason: spell correction

  • May 12, 2019 at 7:03 pm

    #3641063

    Ok... I guess the last question is how you uniquely identify the rows.  What are the columns and datatypes for what could be used as a PK?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 13, 2019 at 7:18 am

    #3641314

    Hello Jeff,

    Member ID which is unique in system. Data type is BIG INT - Thanks!

     

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

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