Update/Insert Records From Daily File - How?

  • Here is my situation: I need to create a stored procedure that will run daily upon receiving a daily file of Updated Data and New Data. 

    What I first did was copy over everything from a table on Day 0, and say on Day 1, I begin receiving a daily file of records that have been updated/inserted on the day before, Day 0. 

    My main table has two fields as primary keys, which with this combination, makes them unique.  I usually like working w/ one primary key, but this is something new and I thought it was the best design.

    So now, I'm trying to created a stored procedure that follows the following psuedo code:

    IF Field1 AND Field2 EXISTS IN

       (SELECT Field1 AND Field2

        FROM tblAllRecords)

    THEN UPDATE tblAllRecords

      WITH Updated Records

    ELSE INSERT INTO tblAllRecords

      ALL values

    Hope what I am trying to do makes sense.  I have a pretty good library of reference books but I can't seem anything to help me out here.  Thank you in advance for any and all advice!

  • If I work on the assumption that you have a table 'tblNewRecords' that contains the daily updates, you can work along these lines:

    --First do the updates

    update all

    set all.* = new.* -- fields as required

    from tblAllRecords all

    inner join tblNewRecords new

    on new.field1 = all.field1 and new.field2 = all.field2

    --Now the inserts

    insert into tblAllRecords(field_list)

    select field_list from tblNewRecords new

    left outer join tblAllRecords all

    on new.field1 = all.field1 and new.field2 = all.field2

    where all.field1 is null and all.field2 is null

    Regards

    Phil

    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

  • I would change the second query like this.

    insert into tblAllRecords(field_list)

    select field_list from tblNewRecords new

    where not exists( select 1 from tblAllRecords where field1 = new.field1 and field2 = new.field2)

    Thanks,

    Ganesh

    Have a nice day, unles you have other plans.

  • OR on the assumption that your new daily data file has all the updates and inserts:

    Use a DTS package to Truncate yourTable

    Then

    Import the New/Revised Data

    This can be done in one package.


    Butch

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

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