Post reply

DTS and Data Integrity.

  • September 15, 2003 at 7:41 am

    #159911

    I am rewriting a database that was written in Visual FoxPro and is currently being hosted by an external company.

    I have written lots of DTS packages to pull the data in, and this works fine. I import the entire database into SQL server as is first, and then run another DTS package to pull the data into the new tables. As they seem to have no database integrity rules, I have had to write stored procedures to format the data before importing it, and new mistakes keep creeping in, so I have to continually review and alter the stored procedures.

    My problem is that I now want to start setting up constraints, relationships, indexes etc. The data is continually being updated by the external company and we get an update CD-ROM once a week. However the update CD holds the entire database on it, so I have no way of knowing which records have been updated, therefore I have to truncate the entire database every week before importing it again.

    If I start creating foreign keys, indexes etc, I will have to disable (or drop) them every week for the import and then re-enable them. I guess I can do this with stored procedures.... I was wondering if this is the only way to achieve what I want to do? (The external company do not know we are rewriting their datbase so I can't ask them for just the data that is updated every week)

  • September 15, 2003 at 6:11 pm

    #474283

    I'd start with your empty database first. Add all your foreign keys, indexes, etc..., this is your 'live' database. Then create another set of tables, these tables become your 'staging' environment. The staging tables don't have any FK's and minimal indexes.

    Then you import into your staging tables and run stored procedures to update/insert the live tables. You can handle exceptions in a variety of methods, eg: add default values, insert into exception tables, exclude record entirely.

    Depending on the number of records you're processing, you might be able to leave the FK's and indexes on the live tables. Naturally you should test a few scenarios to see what suits your situation.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • September 16, 2003 at 1:48 am

    #474318

    Thanks for the response.. I have a few quick questions though..

    Would the 'staging' database be an exact copy of the 'live' database?

    As I get an entire copy of the database every week, I can easily know which entries are inserts, but how will I know which records are updates, since in effect every week the whole database is an insert.

  • September 16, 2003 at 2:07 am

    #474320

    The staging tables would represent the data you receive each week. You clear and re-load them each time you perform a load.

    Once you have loaded the staging tables it is a simple matter of an update query where fields don't match,

    EG.

    

    Update LiveTable1

    Set LiveField1 = StageField1

    , LiveField2 = StageField2

    FROM LiveTable1

    inner join stagetable1

    on livetable1.keyfield = stagetable1.keyfield

    Where LiveField1 <> StageField1

     or LiveField2 <> StageField2

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

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

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