Post reply

Setting up SQL Server for regular imports

  • January 15, 2018 at 5:18 am

    #409957

    Hi all

    I'm setting up a SQL Database to host consolidated data from a number of separate databases.  I think I've sorted the structure out, but I not sure on how to best set up the database for updates.

    For a little background, we have a number of Access databases that hold customer information for a given region.  I'm trying to consolidate this information into a SQL Database with a very similar structure, but with consolidated keys that include the branch ID.  That way I can store the same customer number for each branch in one database.

    We're looking to update that daily, but my question is, what is the best way to set up SQL Server?  Here are some of the limitations I have. 

    • Data in the access databases can be changed, so I can't just upload the new rows
    • There is no time stamp on these field either, so no way to see what's been changed

    I've thought about a few options so far, which each have pros and cons, so I thought I'd ask the experts (you guys) what the best course of action would be.  Here is what I've thought of so far

    1. Drop and create the data tables in SQL, then upload a fresh set of data each time
    2. clear and truncate the tables in SQL, and upload a fresh set of data each time
    3. Compare Access to SQL in Access and update, then upload new data
    4. Upload all data to Temp Table in SQL and get SQL to compare and update

    That's what I've come up with so far, and I think option 4 is probably the best.  I've not used temp tables before, but I think it'd work.

    Does anyone else have a better solution?

    Thank in advance
    Huw

  • January 15, 2018 at 5:35 am

    #1975885

    Hi Huw
    I think the first thing to take into consideration is, do you want to be able to store any of the changes to those customers?
    ie. If I customer's mobile number or address changes, do you want to be able to see what it was at a certain point in time?

  • January 15, 2018 at 5:42 am

    #1975886

    I won't want to change any data in those fields. There are only a handful of things that will need to be recorded in this database, and I'll keep them in a separate table/tables and update them back into the Access database at regular intervals.

    Huw

  • January 15, 2018 at 5:55 am

    #1975887

    What is the purpose of writing the data from your new central database back to the individual Access databases?

    If you are not interested in recording any history (changes over time) then go with option 2 (Truncate the tables and insert the latest data)
    If you do want to be able to track any changes that happened then look at the principals behind SCD (Slowly Changing Dimension) tables:
    https://www.kimballgroup.com/2008/08/slowly-changing-dimensions/
    https://www.kimballgroup.com/2008/11/design-tip-107-using-the-sql-merge-statement-for-slowly-changing-dimension-processing/

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

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